Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.stehule@xxxxxxxxx]
Sent: Wednesday, August 13, 2008 7:51 AM
To: Bart Grantham
Cc: pgsql-general@xxxxxxxxxxxxxx
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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux