Re: RETURNS SETOF table; language 'sql'

On Wed, 2005-11-09 at 13:49 -0500, Tom Lane wrote:
> Mario Splivalo <mario(dot)splivalo(at)mobart(dot)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