SRF problem
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%ROWTYPE;
begin
R.prooftype := ''INCINV'';
R.id := 1;
R.sn := ''012334'';
R.partnername := ''someone'';
R.performancedate := ''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
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 messageWARNING: 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
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
-----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)
iD8DBQFAfjs71Xdt0HKSwgYRAmIvAJ9nQzUtVpT4egB+xAOI4DPyVk/OmwCfQ7qi
wQzeVMejRmZEm2SLLwko3a4=
=E/In
-----END PGP SIGNATURE-----
Richard,
you are right! It was early in the morning... :)
Thank you!
--csaba egy�d
Show quoted text
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: 2004. �prilis 15. 9:31
To: csegyud@vnet.hu; Pgsql-General@Postgresql.Org (E-mail)
Subject: Re: [GENERAL] SRF problemOn 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 messageWARNING: plpgsql: ERROR during compile of check_close2 near line 11
ERROR: return type mismatch in function returning tuple ator 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.