[postgis-users] OT How do I return a list of id's from a stored procedure from a dynamicly generated insert?

[rox]

On Wed, 07 Sep 2011 17:10:59 -0400, rox wrote:
> Postgres 8.3 [altho if 8.4 is required to solve this, that is 
> possible]

sorry - OT should have been on the postgres list.  In case someone 
searches
and finds the question, resolution shown below:

CREATE OR REPLACE FUNCTION test_insert(p_table_name varchar) RETURNS 
TABLE (i int) AS $f$
DECLARE
	qs1 varchar(3000);
BEGIN

	qs1 :=  'INSERT INTO ' || p_table_name ;
	qs1 :=  qs1 || ' (table_id,name) ';
	qs1 :=  qs1 || '(';
	qs1 :=  qs1 || 	   ' SELECT nextval(''test_inserts_table_id_seq''),';
	qs1 :=  qs1 || 	   	' county_name';
	qs1 :=  qs1 || 	   ' FROM counties';
	qs1 :=  qs1 || ')';
	qs1 :=  qs1 || 'RETURNING table_id';
	qs1 :=  qs1 || ';';

  RAISE INFO 'query:%',qs1;

  	RETURN QUERY EXECUTE qs1 ;
EXCEPTION
	WHEN OTHERS THEN
		RAISE INFO 'error:% %',SQLSTATE,sqlerrm;

	RETURN QUERY select -1;
END;
$f$ LANGUAGE plpgsql VOLATILE;

> executing via
>
> select test_insert('test_inserts') as intarray;

change to select test_insert('test_inserts')