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

Interesting. After some toying around I've figured out that this produces "ERROR: structure of query does not match function result type":

RETURN QUERY SELECT connection_id, connection_type_id, connector_node_id,
connector_node_type_id, connectee_node_id, connectee_node_type_id,
current, timestamp::timestamp, $2+1
FROM connections WHERE connection_type_id = 1 AND connector_node_id = ANY($1);

Whereas this doesn't:

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

Is there something about timestamps that prevents plpgsql from seeing where casting is unnecessary? For the record, that field in the function's return type is definitely a timestamp, same for that column in the table. And I know it's bad form for me to have named the column "timestamp" when that's the name of the type. Oops. :)

Thanks for the help, I've shaved about 30% off the query time now that I can use RETURN QUERY.

BG

-----Original Message-----
From: Pavel Stehule [mailto:pavel(dot)stehule(at)gmail(dot)com]
Sent: Wednesday, August 13, 2008 7:51 AM
To: Bart Grantham
Cc: pgsql-general(at)postgresql(dot)org
Subject: 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