Postgres 8.3 [altho if 8.4 is required to solve this, that is possible]
I can't seem to figure out how to validly capture/return the values.
CREATE OR REPLACE FUNCTION test_insert(p_table_name varchar) RETURNS
integer[] AS $f$
DECLARE
qs1 varchar(3000);
return_ids integer[];
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 || ';';
EXECUTE qs1 INTO return_ids;
RETURN returns_ids;
EXCEPTION
WHEN OTHERS THEN
RAISE INFO 'error:% %',SQLSTATE,sqlerrm;
RETURN (-1);
END;
$f$ LANGUAGE plpgsql VOLATILE;
executing via
select test_insert('test_inserts') as intarray;
returns:
INFO: error:22P02 array value must start with "{" or dimension
information
ERROR: array value must start with "{" or dimension information
CONTEXT: PL/pgSQL function "test_insert" while casting return value to
function's return type
********** Error **********
ERROR: array value must start with "{" or dimension information
SQL state: 22P02
Context: PL/pgSQL function "test_insert" while casting return value to
function's return type
I've tried creating the array aggregator in 8.3 and using it on the
return... eg. RETURNING array_agg(table_id).. but aggregates cannot be
used "there".
Any suggestions?
thanks,
Roxanne
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
