From: Gabriel Dovalo Carril <dovalo@terra.es>
To: Jeff Eckermann <jeff_eckermann@yahoo.com>
Cc: Lista SQL de postgres <pgsql-sql@postgresql.org>
Subject: Re: Functions with dynamic queries
Date: Sat, 25 May 2002 03:11:01 +0200
Message-ID: <3CEEE4A5.EF729BB@terra.es> (raw)
References: <20020524135106.99232.qmail@web20802.mail.yahoo.com>
> What do you get when you just run the SQL from the
> command line?
It works fine in command line .
> I am assuming that something is actually returned for
> both sums: if there are no values for either "stkreal"
> or "stkpteser" for "codarticulo = '020220064'", then a
> null will be returned, which will turn the final
> result into a null...
> If there are null values anywhere in those columns,
No, there are no NULL values.
Try attached scripts. They are only examples, (no real data)
They are very, very simple and fails in the same situation.
The only difference between then is line 19
(func_error.sql)
Select people.age into person
(func_nodata.sql)
Select max(people.age) as age into person
The first one returns:
ERROR: record person is unassigned yet
And the second one:
get_age
---------
(1
row)
--
Gabriel Dovalo.
Attachments:func_error.sql (text/plain, 516B) download
Create table people (
nif char(12),
age int4,
primary key (nif)
);
insert into people (nif, age) values (
'001', 10);
insert into people (nif, age) values (
'002', 30);
Create Function get_age(text) Returns text as '
Declare
param_nif Alias For $1;
person record;
text_to_return text;
Begin
Select people.age into person
From people
Where people.nif = param_nif;
text_to_return := person.age::text;
Return text_to_return;
End;
' language 'plpgsql';
Select get_age('001');
func_nodata.sql (text/plain, 528B) download
Create table people (
nif char(12),
age int4,
primary key (nif)
);
insert into people (nif, age) values (
'001', 10);
insert into people (nif, age) values (
'002', 30);
Create Function get_age(text) Returns text as '
Declare
param_nif Alias For $1;
person record;
text_to_return text;
Begin
Select max(people.age) as age into person
From people
Where people.nif = param_nif;
text_to_return := person.age::text;
Return text_to_return;
End;
' language 'plpgsql';
Select get_age('001');
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dovalo@terra.es, jeff_eckermann@yahoo.com
Subject: Re: Functions with dynamic queries
In-Reply-To: <3CEEE4A5.EF729BB@terra.es>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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