SRF problem

[Együd Csaba]

Hi All,
I'd like to write an SRF but I'm block a strange error message

WARNING: plpgsql: ERROR during compile of check_close2 near line 11
ERROR: return type mismatch in function returning tuple at or near ";"

I get this message even if the return rowset contains 0 or more tuples. What
is done wrong?
The code:
-----------------------------------------------------------------
create type CHECK_CLOSE as (
prooftype text,
id int,
sn text,
partnername text,
performancedate text,
detailcount int
);
------------------------------------------------------------------
create or replace function check_close2() returns CHECK_CLOSE AS '
declare
R CHECK_CLOSE%ROW TYPE;
begin
R.prooftype := ''INCINV'';
R.id := 1;
R.sn := ''012334'';
R.partnername := ''someone'';
R.performanceda te := ''2004.02.01'';
R.detailcount := 0;
return next R;
return;
end;'
language 'plpgsql';
------------------------------------------------------------------
select * from check_close2();
WARNING: plpgsql: ERROR during compile of check_close2 near line 11
ERROR: return type mismatch in function returning tuple at or near ";"
Thank you very much.
-- Csaba Együd
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Richard Huxton]

On Thursday 15 April 2004 07:51, Együd Csaba wrote:

Hi All,
I'd like to write an SRF but I'm block a strange error message

WARNING: plpgsql: ERROR during compile of check_close2 near line 11
ERROR: return type mismatch in function returning tuple at or near ";" create or replace function check_close2() returns CHECK_CLOSE AS '

This should be SET OF CHECK_CLOSE, remember you're calling the function like:
SELECT * FROM check_close2()
rather than:
SELECT check_close2()

So the return-type needs to be "set of" even if only returning one value.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Holger Klawitter]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

create or replace function check_close2() returns CHECK_CLOSE AS '

"return next" needs sets as a return type.

create or replace function check_close2() returns setof check_close ...
^^^^^

should work.

Mit freundlichem Gruß / With kind regards
Holger Klawitter
- --
lists <at> klawitter <dot> de
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQFAfjs71Xd t0HKSwgYRAmIvAJ 9nQzUtVpT4egB+x AOI4DPyVk/OmwCfQ7qi
wQzeVMejRmZEm2S LLwko3a4=
=E/In
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Richard Huxton]

On Thursday 15 April 2004 07:51, Együd Csaba wrote:

Hi All,
I'd like to write an SRF but I'm block a strange error message

Oh, and there's a good article on SRF at http://techdocs.postgresql.org

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Együd Csaba]

Richard,
you are right! It was early in the morning... :)

Thank you!
--csaba együd

-----Original Message-----
From: Richard Huxton [mailto:de*@arch onet.com]
Sent: 2004. április 15. 9:31
To: cs*****@vnet.hu; Pg***********@P ostgresql.Org (E-mail)
Subject: Re: [GENERAL] SRF problem
On Thursday 15 April 2004 07:51, Együd Csaba wrote:

Hi All,
I'd like to write an SRF but I'm block a strange error message

WARNING: plpgsql: ERROR during compile of check_close2 near line 11
ERROR: return type mismatch in function returning tuple at

or near ";"

create or replace function check_close2() returns CHECK_CLOSE AS '

This should be SET OF CHECK_CLOSE, remember you're calling
the function like:
SELECT * FROM check_close2()
rather than:
SELECT check_close2()

So the return-type needs to be "set of" even if only
returning one value.
--
Richard Huxton
Archonet Ltd
-- Incoming mail is certified Virus Free.
Checked by AVG Anti-Virus (http://www.grisoft.com).
Version: 7.0.230 / Virus Database: 262 - Release Date: 2004. 04. 15.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)