Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 42962475A75
	for <pgsql-sql@postgresql.org>; Fri, 24 May 2002 22:11:59 -0400 (EDT)
Received: from tsmtp2.mail.isp (mailhost2.teleline.es [195.235.113.141])
	by postgresql.org (Postfix) with ESMTP id 7E339475A59
	for <pgsql-sql@postgresql.org>; Fri, 24 May 2002 22:11:56 -0400 (EDT)
Received: from terra.es ([212.166.173.153]) by tsmtp2.mail.isp
	(Netscape Messaging Server 4.15 tsmtp2 Mar 14 2002 21:29:48)
	with ESMTP id GWNA1N01.IKH; Sat, 25 May 2002 04:10:35 +0200
Message-ID: <3CEEE4A5.EF729BB@terra.es>
Date: Sat, 25 May 2002 03:11:01 +0200
From: Gabriel Dovalo Carril <dovalo@terra.es>
X-Mailer: Mozilla 4.76 [es] (X11; U; Linux 2.4.0-4GB i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Jeff Eckermann <jeff_eckermann@yahoo.com>
Cc: Lista SQL de postgres <pgsql-sql@postgresql.org>
Subject: Re: Functions with dynamic queries
References: <20020524135106.99232.qmail@web20802.mail.yahoo.com>
Content-Type: multipart/mixed; boundary="------------89D412F89A4F982DB5EA6676"
X-Archive-Number: 200205/272
X-Sequence-Number: 7907

Este es un mensaje multipartes en formato MIME.
--------------89D412F89A4F982DB5EA6676
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

> 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.
--------------89D412F89A4F982DB5EA6676
Content-Type: text/plain; charset=us-ascii; name="func_error.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="func_error.sql"


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');


--------------89D412F89A4F982DB5EA6676
Content-Type: text/plain; charset=us-ascii; name="func_nodata.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="func_nodata.sql"


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');


--------------89D412F89A4F982DB5EA6676--