Thread: what exactly is a query structure?

hello.

Postgresql 8.3.9

CREATE TYPE usr_secrets AS (login TEXT, pass TEXT, shop_pass TEXT);

CREATE OR REPLACE FUNCTION get_noobs () RETURNS SETOF usr_secrets AS $$
BEGIN  RETURN QUERY SELECT login, '*' as pass, shop_pass FROM noob;  RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

SELECT * from get_noobs();

And we have the following error

ERROR:  structure of query does not match function result type

In response to silly sad :
> hello.
> 
> Postgresql 8.3.9
> 
> CREATE TYPE usr_secrets AS (login TEXT, pass TEXT, shop_pass TEXT);
> 
> CREATE OR REPLACE FUNCTION get_noobs () RETURNS SETOF usr_secrets AS $$
> BEGIN
>   RETURN QUERY SELECT login, '*' as pass, shop_pass FROM noob;
>   RETURN;
> END;
> $$ LANGUAGE plpgsql SECURITY DEFINER;
> 
> SELECT * from get_noobs();
> 
> And we have the following error
> 
> ERROR:  structure of query does not match function result type

Wild guess: your table noob has an other structure as expected, in
particular login and/or shop_pass are not TEXT.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

On 02/26/10 10:19, A. Kretschmer wrote:
> In response to silly sad :
>> hello.
>>
>> Postgresql 8.3.9
>>
>> CREATE TYPE usr_secrets AS (login TEXT, pass TEXT, shop_pass TEXT);
>>
>> CREATE OR REPLACE FUNCTION get_noobs () RETURNS SETOF usr_secrets AS $$
>> BEGIN
>>    RETURN QUERY SELECT login, '*' as pass, shop_pass FROM noob;
>>    RETURN;
>> END;
>> $$ LANGUAGE plpgsql SECURITY DEFINER;
>>
>> SELECT * from get_noobs();
>>
>> And we have the following error
>>
>> ERROR:  structure of query does not match function result type
>
> Wild guess: your table noob has an other structure as expected, in
> particular login and/or shop_pass are not TEXT.

they are texts.

if we substitute constant '*' with a text field or even a subselect, the 
error disappear.

On 02/26/10 09:50, silly sad wrote:
> hello.
>
> Postgresql 8.3.9
>
> CREATE TYPE usr_secrets AS (login TEXT, pass TEXT, shop_pass TEXT);
>
> CREATE OR REPLACE FUNCTION get_noobs () RETURNS SETOF usr_secrets AS $$
> BEGIN
> RETURN QUERY SELECT login, '*' as pass, shop_pass FROM noob;
> RETURN;
> END;
> $$ LANGUAGE plpgsql SECURITY DEFINER;
>
> SELECT * from get_noobs();
>
> And we have the following error
>
> ERROR: structure of query does not match function result type
>


my own wild guess:
string constant '*' is of type "unknown"

In response to silly sad :
> 
> my own wild guess:
> string constant '*' is of type "unknown"

Maybe. Add a explicit cast, for instance '*'::text

Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

"A. Kretschmer" <andreas.kretschmer@schollglas.com> writes:
> In response to silly sad :
>> my own wild guess:
>> string constant '*' is of type "unknown"

> Maybe. Add a explicit cast, for instance '*'::text

Definitely.  More recent versions of PG provide a more explicit error
message:

regression=# SELECT * from get_noobs();
ERROR:  structure of query does not match function result type
DETAIL:  Returned type unknown does not match expected type text in column "pass".
CONTEXT:  PL/pgSQL function "get_noobs" line 2 at RETURN QUERY
        regards, tom lane