plpgsql function returning SETOF

Поиск
Список
Период
Сортировка
От Johannes Brügmann
Тема plpgsql function returning SETOF
Дата
Msg-id 5zirti30os.fsf@jottbee.net
обсуждение исходный текст
Ответы Re: plpgsql function returning SETOF  ("A. Kretschmer" <andreas.kretschmer@schollglas.com>)
Список pgsql-novice
Hello all novices and experts,

I didn't understand how to create a function, which returns a varying
number of records. Worse, it seems, that I didn't understand the SETOF
feature at all... Two examples:

1.)

should give two records in the year 1990: 1990.06.17 and 1990.10.03,
all other years have minimum 1 NULL record (so at some later point I
can filter with WHERE or so; only for timezone = 'CET' and datestyle =
'German')

       CREATE OR REPLACE FUNCTION tagderdeutscheneinheit(TIMESTAMP WITH TIME ZONE)
       RETURNS SETOF TIMESTAMP WITH TIME ZONE
       CALLED ON NULL INPUT STABLE AS $$
       DECLARE
            in_ts timestamp with time zone;
            rest_ts varchar(14);
                yyyy smallint;
       BEGIN
            IF $1 IS NULL
                THEN in_ts := localtimestamp(0);
                ELSE in_ts := $1;
            END IF;

           yyyy := to_number(date_part('year',in_ts),'9999');
           rest_ts := to_char(date_part('year',in_ts),'9999') || to_char(date_part('hour',in_ts),'99')
                  || to_char(date_part('minute',in_ts),'99') || to_char(date_part('seconds',in_ts),'99');

           SELECT CASE WHEN yyyy > 1951 AND yyyy <= 1990 THEN to_timestamp('03.10.' || rest_ts,'DD MM YYYY HH24 MI SS')
                    ELSE NULL
               END
        UNION ALL
        SELECT CASE WHEN yyyy >= 1990 THEN to_timestamp('17.06.' || rest_ts,'DD MM YYYY HH24 MI SS')
                    ELSE NULL
               END;

       END;
       $$ LANGUAGE plpgsql;

what i get:
      ERROR:  SELECT query has no destination for result data

2.)

similar to the above function, the below have been declared; each of
them returns exactly one value of type timestamp with time zone.

      CREATE TYPE feiertag AS (bezeichnung VARCHAR(100), datum TIMESTAMP WITH TIME ZONE);

      CREATE OR REPLACE FUNCTION feiertage(TIMESTAMP WITH TIME ZONE)
      RETURNS SETOF feiertag
      CALLED ON NULL INPUT AS $$
      DECLARE
           in_ts timestamp with time zone;
      BEGIN
           IF $1 IS NULL
               THEN in_ts := localtimestamp(0);
               ELSE in_ts := $1;
           END IF;

              SELECT 'Allerheiligen             ', allerheiligen(in_ts)
          UNION SELECT 'Aschermittwoch            ', aschermittwoch(in_ts)
        ...
          UNION SELECT 'Tag der deutschen Einheit ', tagderdeutscheneinheit(in_ts)
          UNION SELECT 'Zweiter Weihnachtstag     ', zweiterweihnachtstag(in_ts)  ;
      END;
      $$ LANGUAGE plpgsql;


a result "table" like this is what I want:

       bezeichnung     | datum
      -----------------+-------
       Allerheiligen   | ....

what i get:

      bruegmann@traffic_nrw=# select feiertage(NULL);
      ERROR:  SELECT query has no destination for result data
      HINT:  If you want to discard the results, use PERFORM instead.
      CONTEXT:  PL/pgSQL function "feiertage" line 9 at SQL statement

What I don't understand here is: this is a fixed number of records, a
composit type for setof has been declared, so what else is missing or
is wrong?

Thanks for any help,
Johannes


В списке pgsql-novice по дате отправления:

Предыдущее
От: Randy Long
Дата:
Сообщение: GIS and postgresql
Следующее
От: Tom Lane
Дата:
Сообщение: Re: GIS and postgresql