Re: Functions with dynamic queries - Mailing list pgsql-sql

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


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