Re: PostgreSQL equivalent to Oracles ANYDATASET

[Dirk Mika <Dirk.Mika@xxxxxxxxxxxxx>]

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@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxx>
Datum: Freitag, 13. November 2020 um 18:23
An: Dirk Mika <Dirk.Mika@xxxxxxxxxxxxx>
Cc: "pgsql-general@xxxxxxxxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
Betreff: Re: PostgreSQL equivalent to Oracles ANYDATASET

 

## Dirk Mika (Dirk.Mika@xxxxxxxxxxxxx):

 

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