pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRETURNS SETOF table; language 'sql'
6+ messages / 3 participants
[nested] [flat]
* RETURNS SETOF table; language 'sql'
@ 2005-11-09 15:10 Mario Splivalo <mario.splivalo@mobart.hr>
0 siblings, 2 replies; 6+ messages in thread
From: Mario Splivalo @ 2005-11-09 15:10 UTC (permalink / raw)
To: pgsql-sql
When I issue something like this:
SELECT * FROM ads WHERE id=1004;
i get:
id | vpn_id | service_id | ignore_length | start_time |
end_time | ad_text
------+--------+------------+---------------+------------------------+------------------------+----------------------------
1004 | 1 | 106 | f | 2005-01-01 00:00:00+01 |
2005-12-31 00:00:00+01 | Probna reklama numera una!
Now, I create a function that does that:
CREATE FUNCTION get_ads(int4)
RETURNS SETOF ads
AS
'SELECT * FROM ads WHERE id=$1'
LANGUAGE 'sql'
When I do:
SELECT * FROM get_ads(1004);
i get:
ERROR: query-specified return row and actual function return row do not
match
Why is that?
Mike
P.S. That's run on Postgres 7.4.
--
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: RETURNS SETOF table; language 'sql'
@ 2005-11-09 18:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mario Splivalo <mario.splivalo@mobart.hr>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2005-11-09 18:49 UTC (permalink / raw)
To: mario.splivalo@mobart.hr; +Cc: pgsql-sql
Mario Splivalo <mario.splivalo@mobart.hr> writes:
> When I do:
> SELECT * FROM get_ads(1004);
> i get:
> ERROR: query-specified return row and actual function return row do not
> match
The example as given works fine for me in 7.4.9. Maybe you need an
update, or maybe you're not telling us the whole truth about what you
did.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: RETURNS SETOF table; language 'sql'
@ 2005-11-09 21:28 Mario Splivalo <mario.splivalo@mobart.hr>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Mario Splivalo @ 2005-11-09 21:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Wed, 2005-11-09 at 13:49 -0500, Tom Lane wrote:
> Mario Splivalo <mario.splivalo@mobart.hr> writes:
> > When I do:
> > SELECT * FROM get_ads(1004);
> > i get:
> > ERROR: query-specified return row and actual function return row do not
> > match
>
> The example as given works fine for me in 7.4.9. Maybe you need an
> update, or maybe you're not telling us the whole truth about what you
> did.
Oh, me, big liar :)
I dunno what else could be wrong... Maybe because the id column in table
ads has default value taken from a sequence?
Here are the statements for creating the table, and INSERTS for the
data. That's very same amount of data as I have on my server. I just
removed the default value for the id column, and foreign keys to some
other tables:
CREATE TABLE ads
(
id int4 NOT NULL,
vpn_id int4 NOT NULL,
service_id int4,
ignore_length bool NOT NULL,
start_time timestamptz NOT NULL,
end_time timestamptz,
ad_text varchar(1024) NOT NULL
)
WITHOUT OIDS;
INSERT INTO ads VALUES (1004, 1, 106, false, '2005-01-01 00:00:00+01',
'2005-12-31 00:00:00+01', 'Probna reklama numera una!');
INSERT INTO ads VALUES (1005, 1, 106, false, '2005-01-01 00:00:00+01',
'2005-12-31 00:00:00+01', 'Probna reklama numera una!');
INSERT INTO ads VALUES (1006, 1, 106, false, '2005-01-01 00:00:00+01',
'2005-12-31 00:00:00+01', 'Probna reklama numera dua!');
INSERT INTO ads VALUES (1008, 1, 106, false, '2005-01-01 00:00:00+01',
NULL, 'ProbaNull');
INSERT INTO ads VALUES (1007, 1, 106, false, '2005-01-01 00:00:00+01',
'2006-01-01 00:00:00+01', 'ProbaNull');
Now, here is the code for the function:
CREATE FUNCTION get_ad(int4)
RETURNS SETOF ads
AS
'
SELECT * FROM ads WHERE id=$1
'
LANGUAGE 'sql';
I'm doing all of this on Postgres 7.4.9:
pulitzer2=# select version();
version
----------------------------------------------------------------------------------------------------------------------
PostgreSQL 7.4.9 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.2
20050808 (prerelease) (Ubuntu 4.0.1-4ubuntu9)
(1 row)
Now, I created fresh database on the same database cluster, and executed
the CREATE for the table, INSERTs for the data, and CREATE for the
function. Then I run the function:
fun_test=# select * from get_ad(1004);
id | vpn_id | service_id | ignore_length | start_time |
end_time | ad_text
------+--------+------------+---------------+------------------------+------------------------+----------------------------
1004 | 1 | 106 | f | 2005-01-01 00:00:00+01 |
2005-12-31 00:00:00+01 | Probna reklama numera una!
(1 row)
Works fine. I remind you again, this is on a newly created database.
So, I go back to the database I'm developing, and I create function
get_ad_test (I created get_ad using plpgsql, ended up with adding some
more logic):
CREATE FUNCTION get_ad_test(int4)
RETURNS SETOF ads
AS
'
SELECT * FROM ads WHERE id=$1
'
LANGUAGE 'sql';
When I run it, again the same:pulitzer2=# select * from
get_ad_test(1004);
ERROR: query-specified return row and actual function return row do not
match
pulitzer2=#
I went to some other database I have in that same cluster, recreated the
table, and everything works fine. I haven't tried droping the ads table
in the pulitzer database (the developing database the issue appeared).
Maybe I could provide some more detailed log files, or something?
Mike
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: RETURNS SETOF table; language 'sql'
@ 2005-11-09 22:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mario Splivalo <mario.splivalo@mobart.hr>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2005-11-09 22:05 UTC (permalink / raw)
To: mario.splivalo@mobart.hr; +Cc: pgsql-sql
Mario Splivalo <mario.splivalo@mobart.hr> writes:
> Works fine. I remind you again, this is on a newly created database.
Yup, works fine for me too.
> When I run it, again the same:pulitzer2=# select * from
> get_ad_test(1004);
> ERROR: query-specified return row and actual function return row do not
> match
> pulitzer2=#
Ah, I bet I know the problem:
alter table ads add column z int;
<< function still works ... >>
alter table ads drop column z;
<< function no longer works... >>
7.4 isn't too good about coping with dropped columns in rowtypes.
This problem is fixed in 8.0 and up.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: RETURNS SETOF table; language 'sql'
@ 2005-11-09 22:17 Mario Splivalo <mario.splivalo@mobart.hr>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Mario Splivalo @ 2005-11-09 22:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Wed, 2005-11-09 at 17:05 -0500, Tom Lane wrote:
> Mario Splivalo <mario.splivalo@mobart.hr> writes:
> > Works fine. I remind you again, this is on a newly created database.
>
> Yup, works fine for me too.
>
> > When I run it, again the same:pulitzer2=# select * from
> > get_ad_test(1004);
> > ERROR: query-specified return row and actual function return row do not
> > match
> > pulitzer2=#
>
> Ah, I bet I know the problem:
>
> alter table ads add column z int;
> << function still works ... >>
> alter table ads drop column z;
> << function no longer works... >>
>
> 7.4 isn't too good about coping with dropped columns in rowtypes.
> This problem is fixed in 8.0 and up.
So, I should drop the table, recreate it, and then the function should
work ok?
I'll try ASAP, I'll just jump to a gas station for a beer :)
Mike
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: RETURNS SETOF table; language 'sql'
@ 2005-11-10 16:25 codeWarrior <gpatnude@hotmail.com>
parent: Mario Splivalo <mario.splivalo@mobart.hr>
1 sibling, 0 replies; 6+ messages in thread
From: codeWarrior @ 2005-11-10 16:25 UTC (permalink / raw)
To: pgsql-sql
I think its cause you changed your procedure from being written in SQL to
being writtern in PLPGSQL in your second implementation....
Sets of records are returned from a PLPGSQL function with a RETURN statement
... not a SELECT...
Check out the sections of the manual that talk about PLPGSQL....
35.7.1. Returning From a Function
There are two commands available that allow you to return data from a
function: RETURN and RETURN NEXT.
35.7.1.1. RETURN
RETURN expression;RETURN with an expression terminates the function and
returns the value of expression to the caller. This form is to be used for
PL/pgSQL functions that do not return a set.
When returning a scalar type, any expression can be used. The expression's
result will be automatically cast into the function's return type as
described for assignments. To return a composite (row) value, you must write
a record or row variable as the expression.
The return value of a function cannot be left undefined. If control reaches
the end of the top-level block of the function without hitting a RETURN
statement, a run-time error will occur.
If you have declared the function to return void, a RETURN statement must
still be provided; but in this case the expression following RETURN is
optional and will be ignored if present.
35.7.1.2. RETURN NEXT
RETURN NEXT expression;When a PL/pgSQL function is declared to return SETOF
sometype, the procedure to follow is slightly different. In that case, the
individual items to return are specified in RETURN NEXT commands, and then a
final RETURN command with no argument is used to indicate that the function
has finished executing. RETURN NEXT can be used with both scalar and
composite data types; in the latter case, an entire "table" of results will
be returned.
"Mario Splivalo" <mario.splivalo@mobart.hr> wrote in message
news:1131549050.7758.11.camel@ekim...
> When I issue something like this:
>
> SELECT * FROM ads WHERE id=1004;
>
> i get:
>
> id | vpn_id | service_id | ignore_length | start_time |
> end_time | ad_text
> ------+--------+------------+---------------+------------------------+------------------------+----------------------------
> 1004 | 1 | 106 | f | 2005-01-01 00:00:00+01 |
> 2005-12-31 00:00:00+01 | Probna reklama numera una!
>
>
> Now, I create a function that does that:
>
> CREATE FUNCTION get_ads(int4)
> RETURNS SETOF ads
> AS
> 'SELECT * FROM ads WHERE id=$1'
> LANGUAGE 'sql'
>
> When I do:
>
> SELECT * FROM get_ads(1004);
>
> i get:
>
> ERROR: query-specified return row and actual function return row do not
> match
>
> Why is that?
>
> Mike
>
> P.S. That's run on Postgres 7.4.
> --
> Mario Splivalo
> Mob-Art
> mario.splivalo@mobart.hr
>
> "I can do it quick, I can do it cheap, I can do it well. Pick any two."
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-11-10 16:25 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-09 15:10 RETURNS SETOF table; language 'sql' Mario Splivalo <mario.splivalo@mobart.hr>
2005-11-09 18:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-09 21:28 ` Mario Splivalo <mario.splivalo@mobart.hr>
2005-11-09 22:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-09 22:17 ` Mario Splivalo <mario.splivalo@mobart.hr>
2005-11-10 16:25 ` codeWarrior <gpatnude@hotmail.com>
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