pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSRF Functions don't want to return empty tuple
5+ messages / 3 participants
[nested] [flat]
* SRF Functions don't want to return empty tuple
@ 2003-09-29 16:18 GRIMOIS Eric <eric.grimois@cpam-cergypontoise.cnamts.fr>
0 siblings, 2 replies; 5+ messages in thread
From: GRIMOIS Eric @ 2003-09-29 16:18 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SRF Functions don't want to return empty tuple
@ 2003-09-29 16:40 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: GRIMOIS Eric <eric.grimois@cpam-cergypontoise.cnamts.fr>
1 sibling, 1 reply; 5+ messages in thread
From: Stephan Szabo @ 2003-09-29 16:40 UTC (permalink / raw)
To: GRIMOIS Eric <eric.grimois@cpam-cergypontoise.cnamts.fr>; +Cc: 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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SRF Functions don't want to return empty tuple
@ 2003-09-29 16:52 Richard Huxton <dev@archonet.com>
parent: GRIMOIS Eric <eric.grimois@cpam-cergypontoise.cnamts.fr>
1 sibling, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2003-09-29 16:52 UTC (permalink / raw)
To: GRIMOIS Eric <eric.grimois@cpam-cergypontoise.cnamts.fr>; 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SRF Functions don't want to return empty tuple
@ 2003-09-29 17:12 GRIMOIS Eric <eric.grimois@cpam-cergypontoise.cnamts.fr>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 5+ messages in thread
From: GRIMOIS Eric @ 2003-09-29 17:12 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
----- 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
?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SRF Functions don't want to return empty tuple
@ 2003-09-29 18:21 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: GRIMOIS Eric <eric.grimois@cpam-cergypontoise.cnamts.fr>
0 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2003-09-29 18:21 UTC (permalink / raw)
To: GRIMOIS Eric <eric.grimois@cpam-cergypontoise.cnamts.fr>; +Cc: 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-09-29 18:21 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-29 16:18 SRF Functions don't want to return empty tuple GRIMOIS Eric <eric.grimois@cpam-cergypontoise.cnamts.fr>
2003-09-29 16:40 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-09-29 17:12 ` GRIMOIS Eric <eric.grimois@cpam-cergypontoise.cnamts.fr>
2003-09-29 18:21 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-09-29 16:52 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox