Re: RETURNS SETOF table; language '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.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