Re: set value var via execute

[Igor Neyman <ineyman@xxxxxxxxxxxxxx>]

From: Peter Kroon [mailto:plakroon@xxxxxxxxx] 
Sent: Thursday, November 29, 2012 11:01 AM
To: pgsql-general@xxxxxxxxxxxxxx
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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general