Обсуждение: RETURNS SETOF table; language '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."
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
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.9on 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
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
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
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 >