Обсуждение: PostgreSQL equivalent to Oracles ANYDATASET

Поиск
Список
Период
Сортировка

PostgreSQL equivalent to Oracles ANYDATASET

От
Dirk Mika
Дата:

Hello,

 

we’re currently using Oracle and have a few functions in some packages, which are defined e.g. as follows:

 

   FUNCTION get_results(p_id_series series.id_series%TYPE)

      RETURN ANYDATASET

      PIPELINED USING t_series_get_results;

 

It can be called like:

 

SELECT * FROM TABLE(series_pkg.get_results(1));

 

The purpose of this function is to provide a DATASET, which has different columns in the result depending on the passed parameter.

 

Is there any way to achieve something similar in PostreSQL?

 

BR

Dirk

--
Dirk Mika
Software Developer



mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk.mika@mikatiming.de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika


 
Вложения

Re: PostgreSQL equivalent to Oracles ANYDATASET

От
Pavel Stehule
Дата:
Hi

pá 13. 11. 2020 v 17:57 odesílatel Dirk Mika <Dirk.Mika@mikatiming.de> napsal:

Hello,

 

we’re currently using Oracle and have a few functions in some packages, which are defined e.g. as follows:

 

   FUNCTION get_results(p_id_series series.id_series%TYPE)

      RETURN ANYDATASET

      PIPELINED USING t_series_get_results;

 

It can be called like:

 

SELECT * FROM TABLE(series_pkg.get_results(1));

 

The purpose of this function is to provide a DATASET, which has different columns in the result depending on the passed parameter.

 

Is there any way to achieve something similar in PostreSQL?


Currently there is not this possibility

Theoretically you can use a function, that returns refcursor, and outside function you can read this cursor


Regards

Pavel

 

BR

Dirk

--
Dirk Mika
Software Developer



mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk.mika@mikatiming.de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika


 

Вложения

Re: PostgreSQL equivalent to Oracles ANYDATASET

От
Christoph Moench-Tegeder
Дата:
## Dirk Mika (Dirk.Mika@mikatiming.de):

> SELECT * FROM TABLE(series_pkg.get_results(1));
> 
> The purpose of this function is to provide a DATASET, which has
> different columns in the result depending on the passed parameter.
> 
> Is there any way to achieve something similar in PostreSQL?

testing=# CREATE OR REPLACE FUNCTION public.rr(p INTEGER)
 RETURNS SETOF RECORD
 LANGUAGE plpgsql
AS $function$
BEGIN
  IF p = 1 THEN
    RETURN NEXT ('k1'::TEXT, 'v1'::TEXT);
    RETURN NEXT ('k2'::TEXT, 'v2'::TEXT);
  ELSE
    RETURN NEXT (23::INTEGER, 42::INTEGER, 'abc'::TEXT);
    RETURN NEXT (42::INTEGER, 23::INTEGER, 'xyz'::TEXT);
  END IF;
  RETURN;
END;
$function$;
CREATE FUNCTION

testing=# SELECT * FROM rr(2) f(a INTEGER, b INTEGER, c TEXT);
 a  | b  |  c
----+----+-----
 23 | 42 | abc
 42 | 23 | xyz
(2 rows)

testing=# SELECT * FROM rr(1) f(x TEXT, y TEXT);
 x  | y
----+----
 k1 | v1
 k2 | v2
(2 rows)

Regards,
Christoph

-- 
Spare Space

Re: PostgreSQL equivalent to Oracles ANYDATASET

От
Dirk Mika
Дата:

Hi,

 

many thanks for this suggestion. But the problem with this is that you have to know which columns are returned when you call the function.

 

Regards

Dirk

 

--
Dirk Mika
Software Developer



mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk.mika@mikatiming.de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika

Von: Christoph Moench-Tegeder <cmt@burggraben.net>
Datum: Freitag, 13. November 2020 um 18:23
An: Dirk Mika <Dirk.Mika@mikatiming.de>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Betreff: Re: PostgreSQL equivalent to Oracles ANYDATASET

 

## Dirk Mika (Dirk.Mika@mikatiming.de):

 

SELECT * FROM TABLE(series_pkg.get_results(1));

The purpose of this function is to provide a DATASET, which has

different columns in the result depending on the passed parameter.

Is there any way to achieve something similar in PostreSQL?

 

testing=# CREATE OR REPLACE FUNCTION public.rr(p INTEGER)

RETURNS SETOF RECORD

LANGUAGE plpgsql

AS $function$

BEGIN

  IF p = 1 THEN

    RETURN NEXT ('k1'::TEXT, 'v1'::TEXT);

    RETURN NEXT ('k2'::TEXT, 'v2'::TEXT);

  ELSE

    RETURN NEXT (23::INTEGER, 42::INTEGER, 'abc'::TEXT);

    RETURN NEXT (42::INTEGER, 23::INTEGER, 'xyz'::TEXT);

  END IF;

  RETURN;

END;

$function$;

CREATE FUNCTION

 

testing=# SELECT * FROM rr(2) f(a INTEGER, b INTEGER, c TEXT);

a  | b  |  c

----+----+-----

23 | 42 | abc

42 | 23 | xyz

(2 rows)

 

testing=# SELECT * FROM rr(1) f(x TEXT, y TEXT);

x  | y

----+----

k1 | v1

k2 | v2

(2 rows)

 

Regards,

Christoph

 

--

Spare Space

 

Вложения

Re: PostgreSQL equivalent to Oracles ANYDATASET

От
Dirk Mika
Дата:

Hi,

 

That looks promising. I'll take a closer look at that.

 

Regards

Dirk

 

--
Dirk Mika
Software Developer



mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk.mika@mikatiming.de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika

Von: Pavel Stehule <pavel.stehule@gmail.com>
Datum: Freitag, 13. November 2020 um 18:05
An: Dirk Mika <Dirk.Mika@mikatiming.de>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Betreff: Re: PostgreSQL equivalent to Oracles ANYDATASET

 

Hi

 

pá 13. 11. 2020 v 17:57 odesílatel Dirk Mika <Dirk.Mika@mikatiming.de> napsal:

Hello,

 

we’re currently using Oracle and have a few functions in some packages, which are defined e.g. as follows:

 

   FUNCTION get_results(p_id_series series.id_series%TYPE)

      RETURN ANYDATASET

      PIPELINED USING t_series_get_results;

 

It can be called like:

 

SELECT * FROM TABLE(series_pkg.get_results(1));

 

The purpose of this function is to provide a DATASET, which has different columns in the result depending on the passed parameter.

 

Is there any way to achieve something similar in PostreSQL?

 

Currently there is not this possibility

 

Theoretically you can use a function, that returns refcursor, and outside function you can read this cursor

 

 

Regards

 

Pavel

 

BR

Dirk

--
Dirk Mika
Software Developer

cid:175c28eef6b1377a7f41

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk.mika@mikatiming.de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika

 

cid:175c28eef6d822336482


 

Вложения