Re: [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')

_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users