create a variable with dynamic data type in plpgsql

Question

Is there a way to declare a variable in a plpgsql function, which data type is set on a parameter?
For example:

create or replace function test(datatype text)<br> returns void as $$
declare
  data datatype;  -- data type is stored in the text string, for example int
begin
  raise notice '%', (select pg_typeof(data));
end;
$$ language plpgsql;

In other words, i am trying to write a function, and inside that function i need a variable, and that variable data type must be defined in another input parameter. Any ideas? Bests regards

Pavel Stehule Pavel Stehule · Accepted Answer · 2016-12-23T04:55:30

There is one way - you can use polymorphic parameters. The PLpgSQL is limitted in this area - the language is designed as very static - so you can do some dynamic work, but usually this work is hard (sometimes it is signal of unhappy design). The variable should be declared outside as function argument:

CREATE OR REPLACE FUNCTION public.foo(anyelement)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
  RAISE NOTICE '<<%>>', pg_typeof($1);
END;
$function$

postgres=# SELECT foo(NULL::int), foo(NULL::text);
NOTICE:  <<integer>>
NOTICE:  <<text>>
┌─────┬─────┐
│ foo │ foo │
╞═════╪═════╡
│     │     │
└─────┴─────┘
(1 row)

The C language - C functions supports full environment and you can do there much more than in PLpgSQL. PLpgSQL is designed for static strict business processes - where using too dynamic code is usually wrong.

create a variable with dynamic data type in plpgsql

1 Answers