Thread: SRF Functions don't want to return empty tuple

SRF Functions don't want to return empty tuple

From

"GRIMOIS Eric"

Date:

29 September 2003, 16:23:31

Hi

I have a problem with SRF functions on a RedHat 8.0 system with PostgreSQL
7.3.2.

Schema is following :

CREATE TABLE public.agents ( numero_age int4 NOT NULL, nom_age varchar(30) NOT NULL, prenom_age varchar(30) NOT NULL,
date_entree_agedate NOT NULL, identite varchar(50), CONSTRAINT agents_pkey PRIMARY KEY (numero_age)
 
) WITH OIDS;

INSERT INTO public.agents VALUES (78888, 'PERAVIF', 'JO', '1967-06-18', '')

CREATE FUNCTION public.liste_agents(int4) RETURNS public.agents AS 'SELECT *
FROM agents WHERE numero_age = $1'  LANGUAGE 'sql' VOLATILE;

When, I try :

SELECT * FROM liste_agents(78888)

Everything is Ok, I get my agent. But if I try :

SELECT * FROM liste_agents(0) (... or any other numero_age not in the
table...)

I get a error message :

ExecMakeTableFunctionResult: Invalid result from function returning tuple

What's wrong ? I guessed null values returned by the query didn't match
fields declared not null in the table schema, so I create a custom type
(with CREATE TYPE) but it neither doesn't work. Hint (?) : I've activated
Plpython for this Database.

Thanks
Eric GRIMOIS
Concepteur de logiciels
SEI - CPAM du Val d'Oise

Re: SRF Functions don't want to return empty tuple

From

"GRIMOIS Eric"

Date:

29 September 2003, 17:14:58

----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: "GRIMOIS Eric" <eric.grimois@cpam-cergypontoise.cnamts.fr>
Cc: <pgsql-sql@postgresql.org>
Sent: Monday, September 29, 2003 6:40 PM
Subject: Re: [SQL] SRF Functions don't want to return empty tuple


>
> On Mon, 29 Sep 2003, GRIMOIS Eric wrote:
>
> > Hi
> >
> > I have a problem with SRF functions on a RedHat 8.0 system with
PostgreSQL
> > 7.3.2.
> >
> > Schema is following :
> >
> > CREATE TABLE public.agents (
> >   numero_age int4 NOT NULL,
> >   nom_age varchar(30) NOT NULL,
> >   prenom_age varchar(30) NOT NULL,
> >   date_entree_age date NOT NULL,
> >   identite varchar(50),
> >   CONSTRAINT agents_pkey PRIMARY KEY (numero_age)
> > ) WITH OIDS;
> >
> > INSERT INTO public.agents VALUES (78888, 'PERAVIF', 'JO', '1967-06-18',
'')
> >
> > CREATE FUNCTION public.liste_agents(int4) RETURNS public.agents AS
'SELECT *
> > FROM agents WHERE numero_age = $1'  LANGUAGE 'sql' VOLATILE;
>
> Are you sure you don't want setof public.agents if you want to be able to
> return an empty set?
>

Yes, you're right. Now, it works.

Thank you very much

The syntax I used was wrong, but is accepted by Postgres. What does it mean
?

Re: SRF Functions don't want to return empty tuple

From

Richard Huxton

Date:

29 September 2003, 17:26:29

On Monday 29 September 2003 17:18, GRIMOIS Eric wrote:
> Hi
>
> I have a problem with SRF functions on a RedHat 8.0 system with PostgreSQL
> 7.3.2.
>
> Schema is following :
>
> CREATE TABLE public.agents (
>   numero_age int4 NOT NULL,
>   nom_age varchar(30) NOT NULL,
>   prenom_age varchar(30) NOT NULL,
>   date_entree_age date NOT NULL,
>   identite varchar(50),
>   CONSTRAINT agents_pkey PRIMARY KEY (numero_age)
> ) WITH OIDS;
>
> INSERT INTO public.agents VALUES (78888, 'PERAVIF', 'JO', '1967-06-18', '')
>
> CREATE FUNCTION public.liste_agents(int4) RETURNS public.agents AS 'SELECT
> * FROM agents WHERE numero_age = $1'  LANGUAGE 'sql' VOLATILE;

You want "RETURNS SET OF public.agents"

--  Richard Huxton Archonet Ltd

Re: SRF Functions don't want to return empty tuple

From

Stephan Szabo

Date:

29 September 2003, 17:32:25

On Mon, 29 Sep 2003, GRIMOIS Eric wrote:

> Hi
>
> I have a problem with SRF functions on a RedHat 8.0 system with PostgreSQL
> 7.3.2.
>
> Schema is following :
>
> CREATE TABLE public.agents (
>   numero_age int4 NOT NULL,
>   nom_age varchar(30) NOT NULL,
>   prenom_age varchar(30) NOT NULL,
>   date_entree_age date NOT NULL,
>   identite varchar(50),
>   CONSTRAINT agents_pkey PRIMARY KEY (numero_age)
> ) WITH OIDS;
>
> INSERT INTO public.agents VALUES (78888, 'PERAVIF', 'JO', '1967-06-18', '')
>
> CREATE FUNCTION public.liste_agents(int4) RETURNS public.agents AS 'SELECT *
> FROM agents WHERE numero_age = $1'  LANGUAGE 'sql' VOLATILE;

Are you sure you don't want setof public.agents if you want to be able to
return an empty set?

Re: SRF Functions don't want to return empty tuple

From

Stephan Szabo

Date:

29 September 2003, 18:22:02

On Mon, 29 Sep 2003, GRIMOIS Eric wrote:

> > On Mon, 29 Sep 2003, GRIMOIS Eric wrote:
> >
> > > CREATE TABLE public.agents (
> > >   numero_age int4 NOT NULL,
> > >   nom_age varchar(30) NOT NULL,
> > >   prenom_age varchar(30) NOT NULL,
> > >   date_entree_age date NOT NULL,
> > >   identite varchar(50),
> > >   CONSTRAINT agents_pkey PRIMARY KEY (numero_age)
> > > ) WITH OIDS;
> > >
> > > INSERT INTO public.agents VALUES (78888, 'PERAVIF', 'JO', '1967-06-18',
> '')
> > >
> > > CREATE FUNCTION public.liste_agents(int4) RETURNS public.agents AS
> 'SELECT *
> > > FROM agents WHERE numero_age = $1'  LANGUAGE 'sql' VOLATILE;
> >
> > Are you sure you don't want setof public.agents if you want to be able to
> > return an empty set?
> >
>
> Yes, you're right. Now, it works.
>
> Thank you very much
>
> The syntax I used was wrong, but is accepted by Postgres. What does it mean

IIRC it means it returns exactly one object of the type defined by
agents.