Thread: 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
>