Thread: problem with pl/pgsql function unknown parameters

Hello everybody,

I'm building a function that will import data into a postgresql db
(7.2.4). Now i have run into this problem: in my import module I do a
lot of checks to see if a value is empty or null. I want to solve this
with a function, something like this:

    drop function is_empty();
    create function is_not_empty() returns boolean as '
    declare
    begin
      if $1 = NULL or $1 = '''' then
        return true;
      else
        return false;
      end if;
    end; '
    language 'plpgsql';

    select is_empty();
    true
    select is_empty('');
    true
    select is_empty('Blah');
    false

This won't work, pl wants to know at compile time which function
parameters there will be. Another problem is when I don't know the
datatype of the parameter or how many parameters there will be at
compile time.

Can anybody point me in the right direction? Thanks!

A more general matter. I have some finding answers to questions like
these in the material I have here (the pg documentation & website,
Bruce Momjians book, the PHP and Postgresql book, online books,
Google). Is there a book or website that will give in depth explanation
on pl/pgsql? TIA!

Op 11 Feb 2003 (14:26), schreef Dmitry Tkach <dmitry@openratings.com>:

Hello Dmitry, thanks for your reaction.

> Make it text:
> (this example is in sql - I suppose, you could do the same thing in pl):
>
> create or replace function is_empty (text) returns boolean as 'select $1 is null or $1=\'\'' language 'sql' with
(iscachable);
>
> select is_empty('Blah');
> false
> select is_empty ('2');
> false
> select is_empty ('');
> true
> select is_empty (null);
> true

select is_empty();
ERROR:  Function 'is_empty()' does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts

> As for the 'no arg version' (I don't understand why you need it actually),

Defensive programming. I want my functions to behave nicely, even if
they have no idea what's coming at them. If a function gets no
parameter, it could default to a hardcoded value. IMO this is very
useful, I find it strange that postgresql doesn't allow this.

> you'd have to have a separate func:
> create or replace function is_empty () returns boolean as 'select true;' language 'sql'  with (iscachable);
>
> select is_empty();
> true
>
> I hope, it helps...
>
> Dima

Another example of a useful function (semi-code):

function showtype(unspecified) returns text {
    raise notice ''%'', datatype($1);
}

I guess that if I wanted to do this in postgresql I would have to make
a function for every possible datatype. Not very efficient :-(

>
>
>select is_empty();
>ERROR:  Function 'is_empty()' does not exist
>Unable to identify a function that satisfies the given argument types
>You may need to add explicit typecasts
>
>
Yeah... as I said below - you need a separate function for that...

>
>Defensive programming. I want my functions to behave nicely, even if
>they have no idea what's coming at them. If a function gets no
>parameter, it could default to a hardcoded value. IMO this is very
>useful, I find it strange that postgresql doesn't allow this.
>
>
>
>Another example of a useful function (semi-code):
>
>function showtype(unspecified) returns text {
>    raise notice ''%'', datatype($1);
>}
>
>I guess that if I wanted to do this in postgresql I would have to make
>a function for every possible datatype. Not very efficient :-(
>
>
This is a question of taste (or ideology, if you would)...
Some language use strict types, some do not. For example, in perl or
javascript, or (to a lesser extent) in Kernigan-Richie C  you can do all
kinds of tricks like that, in ANSI C, or C++ or Java you cannot... It
looks like stored procedures in postgres follow the latter paradigm...
There are some advantages, and some disadvantages to that, the consencus
among computer scientists nowadays seems to be that advantages are worth
the lost flexibility....

Dima