Re: [GENERAL] Alias for function return buffer in pl/pgsql?

>
>
> 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

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