Postgres Pro
Downloads
Home   >   mailing lists

Thread: SRF problem

SRF problem

From
Együd Csaba
Date:
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

Re: SRF problem

From
Richard Huxton
Date:
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

Re: SRF problem

From
Richard Huxton
Date:
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

Re: SRF problem

From
Holger Klawitter
Date:
-----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-----

Re: SRF problem

From
Együd Csaba
Date:
Richard,
  you are right! It was early in the morning... :)

Thank you!
--csaba együd

> -----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 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.
>