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



[cid:mt_c1c59b3d-dd43-4ca6-992b-79f5a19f5999.png]

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



fon +49 2202 2401-1197
dirk.m...@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 
Datum: Freitag, 13. November 2020 um 18:23
An: Dirk Mika 
Cc: "pgsql-general@lists.postgresql.org" 
Betreff: Re: PostgreSQL equivalent to Oracles ANYDATASET

## Dirk Mika (dirk.m...@mikatiming.de<mailto:dirk.m...@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

[Christoph Moench-Tegeder]

## Dirk Mika (dirk.m...@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