Re: Alias for function return buffer in pl/pgsql? - Mailing list pgsql-general

Interesting.  After some toying around I've figured out that this produces "ERROR:  structure of query does not match
functionresult 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,
thatfield in the function's return type is definitely a timestamp, same for that column in the table.  And I know it's
badform for me to have named the column "timestamp" when that's the name of the type.  Oops. :)
 

BG


-----Original Message-----
From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: Wednesday, August 13, 2008 7:51 AM
To: Bart Grantham
Cc: pgsql-general@postgresql.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$$languageplpgsql;
 
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