> > > Bonus question - if I rewrite the first FOR loop as: > > > > RETURN QUERY SELECT connection_id, connection_type_id, connector_node_id, > connector_node_type_id, connectee_node_id, > > connectee_node_type_id, current, timestamp, $2 > + 1 FROM connections > > WHERE connection_type_id = 1 AND connector_node_id = > ANY($1); you have to cast. This code works: postgres=# create type xxtp as (a integer, b varchar); CREATE TYPE Time: 6,458 ms postgres=# create table xx(a integer, b varchar); CREATE TABLE Time: 54,053 ms postgres=# insert into xx select 1, 'hhh'; INSERT 0 1 Time: 5,993 ms postgres=# insert into xx select 1, 'hhh'; INSERT 0 1 Time: 3,393 ms postgres=# insert into xx select 1, 'hhh'; INSERT 0 1 >postgres=# create or replace function x() returns setof xxtp as $$begin return query select * from xx; return; end$$language plpgsql; CREATE FUNCTION Time: 4,392 ms postgres=# select * from x(); a | b ---+----- 1 | hhh 1 | hhh 1 | hhh (3 rows) postgres=# create or replace function x() returns setof xxtp as $$begin return query select 1,'kkk'; return; end$$language plpgsql; CREATE FUNCTION Time: 4,577 ms postgres=# select * from x(); ERROR: structure of query does not match function result type CONTEXT: PL/pgSQL function "x" line 1 at RETURN QUERY postgres=# create or replace function x() returns setof xxtp as $$begin return query select 1,'kkk'::varchar; return; end$$language plpgsql; CREATE FUNCTION Time: 3,395 ms postgres=# select * from x(); a | b ---+----- 1 | kkk (1 row) regards Pavel Stehule > > > I get "ERROR: structure of query does not match function result type", even > though the type signatures of the returned columns match the > "connection_generation" rowtype. I am pretty sure this could be resolved by > casting the resulting columns to that row type, but I am lost as to how the > syntax to do such a thing would look. > > > > Thanks in advance for the help, and keep up the great work. PG8.3 is an > amazing piece of software and it blows me away how much more advanced it > gets with every release. > > > > Bart Grantham > > VP of R&D > > Logicworks Inc. – Complex and Managed Hosting
