pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedwhat exactly is a query structure?
6+ messages / 3 participants
[nested] [flat]
* what exactly is a query structure?
@ 2010-02-26 06:50 silly sad <sad@bankir.ru>
0 siblings, 2 replies; 6+ messages in thread
From: silly sad @ 2010-02-26 06:50 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: what exactly is a query structure?
@ 2010-02-26 07:19 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: silly sad <sad@bankir.ru>
1 sibling, 1 reply; 6+ messages in thread
From: A. Kretschmer @ 2010-02-26 07:19 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: what exactly is a query structure?
@ 2010-02-26 09:42 silly sad <sad@bankir.ru>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 0 replies; 6+ messages in thread
From: silly sad @ 2010-02-26 09:42 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: what exactly is a query structure?
@ 2010-02-26 09:44 silly sad <sad@bankir.ru>
parent: silly sad <sad@bankir.ru>
1 sibling, 1 reply; 6+ messages in thread
From: silly sad @ 2010-02-26 09:44 UTC (permalink / raw)
To: pgsql-sql
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"
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: what exactly is a query structure?
@ 2010-02-26 09:53 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: silly sad <sad@bankir.ru>
0 siblings, 1 reply; 6+ messages in thread
From: A. Kretschmer @ 2010-02-26 09:53 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: what exactly is a query structure?
@ 2010-02-26 15:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2010-02-26 15:11 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-02-26 15:11 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-26 06:50 what exactly is a query structure? silly sad <sad@bankir.ru>
2010-02-26 07:19 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2010-02-26 09:42 ` silly sad <sad@bankir.ru>
2010-02-26 09:44 ` silly sad <sad@bankir.ru>
2010-02-26 09:53 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2010-02-26 15:11 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox