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

Lists: pgsql-sql
From: "GRIMOIS Eric" <eric(dot)grimois(at)cpam-cergypontoise(dot)cnamts(dot)fr>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: SRF Functions don't want to return empty tuple
Date: 2003-09-29 16:18:00
Message-ID: 003901c386a5$409c0b40$3d04a837@cpamcergypontoise.cnamts.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

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;

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


From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: GRIMOIS Eric <eric(dot)grimois(at)cpam-cergypontoise(dot)cnamts(dot)fr>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: SRF Functions don't want to return empty tuple
Date: 2003-09-29 16:40:06
Message-ID: 20030929093850.X92835@megazone.bigpanda.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql


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?


From: Richard Huxton <dev(at)archonet(dot)com>
To: "GRIMOIS Eric" <eric(dot)grimois(at)cpam-cergypontoise(dot)cnamts(dot)fr>, <pgsql-sql(at)postgresql(dot)org>
Subject: Re: SRF Functions don't want to return empty tuple
Date: 2003-09-29 16:52:39
Message-ID: 200309291752.39350.dev@archonet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

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


From: "GRIMOIS Eric" <eric(dot)grimois(at)cpam-cergypontoise(dot)cnamts(dot)fr>
To: "Stephan Szabo" <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: SRF Functions don't want to return empty tuple
Date: 2003-09-29 17:12:08
Message-ID: 008801c386ac$d08b68c0$3d04a837@cpamcergypontoise.cnamts.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

----- Original Message -----
From: "Stephan Szabo" <sszabo(at)megazone(dot)bigpanda(dot)com>
To: "GRIMOIS Eric" <eric(dot)grimois(at)cpam-cergypontoise(dot)cnamts(dot)fr>
Cc: <pgsql-sql(at)postgresql(dot)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
?


From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: GRIMOIS Eric <eric(dot)grimois(at)cpam-cergypontoise(dot)cnamts(dot)fr>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: SRF Functions don't want to return empty tuple
Date: 2003-09-29 18:21:14
Message-ID: 20030929112022.O94710@megazone.bigpanda.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql


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.