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.