I need to create a pl/pgsql function witch accept a list of parameters and evaluate them.
I've tried this

CREATE OR REPLACE FUNCTION get_first_valid (lista_elementi VARCHAR[])
RETURNS VARCHAR AS $$
DECLARE
the_one VARCHAR;
BEGIN

IF (ARRAY_UPPER(lista_elementi, 1) IS NOT NULL) THEN
FOR i IN 1..ARRAY_UPPER(lista_elementi, 1) LOOP
the_one := lista_elementi[i];
IF ((the_one IS NOT NULL) AND (TRIM(the_one) != '') ) THEN
EXIT;
END IF;
END LOOP;
ELSE
the_one := NULL;
END IF;

RETURN (the_one);
END;
$$ LANGUAGE 'plpgsql' VOLATILE;

I know that postgresql array implementation is not right complete and that if just
one element of array is NULL the basics array function (array_dims, array_upper, etc.) returns NULL.
I need to send a list of parameters (which could contain NULL values) and evaluate the parameters excluding NULL values
is this possible?
any suggestion?


Sgarbossa Domenico

Extacy Project Leader






Le informazioni contenute nella presente email e nei relativi allegati possono essere riservate e sono comunque destinate solo ed esclusivamente alle persone o all'ente sopra indicati come destinatari. La diffusione, distribuzione e/o copiatura del documento trasmesso da parte di qualsiasi soggetto diverso dal destinatario è proibita sia ai sensi dell'art. 616 c.p. e sia ai sensi del dlg 196/03. Se avete ricevuto questo messaggio per errore vi invitiamo a distruggerlo e ad informarci (+39 049 9318300, info@eniac.it). The information in this email, any files transmitted with it included, is confidential and may also be legally privileged. It is intended for the addressee only. Access to this email by anyone else is unauthorised. It is not to be relied upon by any person other than the addressee except with our prior written approval. If no such approval is given, we will not accept any liability (in negligence or otherwise) arising from any third party acting or refraining from acting on such information. Unauthorised recipients are required to maintain confidentially. If you have received this email in error, please notify us immediately (+39 049 9318300, info@eniac.it), destroy any copies and delete it from your computer system. Any use, dissemination, forwarding, printing or copying of this email is prohibited.

Search Discussions

  • Pavel Stehule at Oct 21, 2009 at 9:23 am

    I know that postgresql array implementation is not right complete and that if just
    one element of array is NULL the basics array function (array_dims, array_upper, etc.) returns NULL.
    I need to send a list of parameters (which could contain NULL values) and evaluate the parameters excluding NULL values
    is this possible?
    any suggestion?
    It's not true. Dimensions are stored independent to content.

    postgres=# create or replace function foo(int[])
    returns int as $$
    declare s int = 0; i integer;
    begin
    for i in select generate_subscripts($1,1)
    loop
    s := s + coalesce($1[i],0);
    end loop;
    return s;
    end;
    $$ language plpgsql;
    CREATE FUNCTION
    postgres=# select foo(array[1,2]);
    foo
    ─────
    3
    (1 row)

    postgres=# select foo(array[1,2, null, 3]);
    foo
    ─────
    6
    (1 row)
  • Tom Lane at Oct 21, 2009 at 2:39 pm

    Pavel Stehule writes:
    I know that postgresql array implementation is not right complete and that if just
    one element of array is NULL the basics array function (array_dims, array_upper, etc.) returns NULL.
    It's not true. Dimensions are stored independent to content.
    I wonder if the OP is using an old version of Postgres. Since 8.2 we
    can handle null elements of arrays, but 8.1 and older couldn't --- the
    whole array went to null if you tried to include a null in it.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedOct 21, '09 at 8:37a
activeOct 21, '09 at 2:39p
posts3
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase