Обсуждение: set value var via execute

Поиск
Список
Период
Сортировка

set value var via execute

От
Peter Kroon
Дата:
Is it possible to set the value of a var via execute?




drop table if exists __test;
create unlogged table __test(
id int
);

DO $$

DECLARE
v_holder int;
v_table text = 'table';
v_record_0 text[];
v_id int;

BEGIN

execute '
insert into __test(id)
select id from '||v_table||' order by random() limit 2
';
v_id = (select id from __test limit 1);

 --begin this fails------------------------------------------------------------------------------------------
        v_holder = execute 'select id from '||v_table||' order by random() limit 1';
        --end this fails-------------------------------------------------------------------------------------------

v_record_0 := array(
SELECT id FROM table order by random() --limit 2
);

raise notice '%', v_record_0;
END;

$$ LANGUAGE plpgsql;

Re: set value var via execute

От
Peter Kroon
Дата:
Go it:

        execute 'select id from '||v_table||' order by random() limit 1' into v_holder;


2012/11/29 Peter Kroon <plakroon@gmail.com>
 --begin this fails------------------------------------------------------------------------------------------
        v_holder = execute 'select id from '||v_table||' order by random() limit 1';
        --end this fails-------------------------------------------------------------------------------------------

Re: set value var via execute

От
Igor Neyman
Дата:
From: Peter Kroon [mailto:plakroon@gmail.com]
Sent: Thursday, November 29, 2012 11:01 AM
To: pgsql-general@postgresql.org
Subject: set value var via execute

Is it possible to set the value of a var via execute?

drop table if exists __test;
create unlogged table __test(
    id int
);

DO $$

DECLARE
    v_holder int;
    v_table text = 'table';
    v_record_0 text[];
    v_id int;

BEGIN

    execute '
        insert into __test(id)
        select id from '||v_table||' order by random() limit 2
        ';
    v_id = (select id from __test limit 1);

       --begin this fails------------------------------------------------------------------------------------------
        v_holder = execute 'select id from '||v_table||' order by random() limit 1';
        --end this fails-------------------------------------------------------------------------------------------

    v_record_0 := array(
        SELECT id FROM table order by random() --limit 2
    );

    raise notice '%', v_record_0;
END;

$$ LANGUAGE plpgsql;


Peter,

Instead of:

v_holder = execute 'select id from '||v_table||' order by random() limit 1';

do this:

execute 'select id from '||v_table||' order by random() limit 1' INTO v_holder;

Regards,
Igor Neyman