Subject: Re: RETURNS SETOF table; language 'sql'
From: Mario Splivalo <mario.splivalo@mobart.hr>
Reply-To: mario.splivalo@mobart.hr
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <18100.1131562181@sss.pgh.pa.us>
References: <1131549050.7758.11.camel@ekim> <18100.1131562181@sss.pgh.pa.us>
Content-Type: text/plain
Organization: Mob-Art
Date: Wed, 09 Nov 2005 22:28:20 +0100
Message-Id: <1131571700.24088.16.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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