Postgresql: dump of input key/value pairs into a string variable in PL/pgSQL

Question

I need to find a way to dump key/value pairs of PL/pgSQL function input parameters:

CREATE OR REPLACE FUNCTION _test(A text, B text)
...
raise info 'Params dump: %', _x;
...

when executed:

select _text('HELLO', 'WORLD');

the function raises info as follows:

'A = HELLO, B = WORLD'

Is there a way to dump input parameter key/value pairs into a variable?

No, it is not possible in PL/pgSQL - probably you can write some extension like PL debugger, that can do it. — Pavel Stehule
– Pavel Stehule, Commented Sep 7, 2012 at 5:21
I found some info re extensions where input arguments can be referenced like in a regular array. On another hand plpgsql has syntax like $1, $2 etc for input parameters. Maybe there is some way to loop over them? Or reference as an array? If I could have a list of all parameter values without keys - it is also ok. Unfortunately extensions can not be added for this task due to sys config. — Gonki
– Gonki, Commented Sep 7, 2012 at 7:29
@Endorphin Nope, you can't reference them dynamically as $1, $2, etc. The only way you could do that is EXECUTE and the EXECUTEd statement can't reference PL/PgSQL variables so that won't work. There is no way to get the parameters as a RECORD. It wouldn't make sense to get them as an ARRAY because the arguments might be of differing parameter types. — Craig Ringer
– Craig Ringer, Commented Sep 7, 2012 at 10:57

Craig Ringer · Accepted Answer · 2012-09-07 07:40:53Z

1

It's possible if you can make the function VARIADIC with uniform argument types, and can print the array. You don't get argument names, since they don't have names, but you do get argument positions.

Otherwise no, it is not possible in PL/PgSQL, though it should be in other PLs like PL/Perl, PL/Python, etc.

It'd be quite nice to be able to get a RECORD with all the function arguments in it, so you could print it, feed it to the hstore extension, etc, but this isn't currently possible.

answered Sep 7, 2012 at 7:40

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gonki Over a year ago

Thanks. These suggestions would not work in our env so have to hard-code all parameters into a string across many functions to enable their logging.

Gonki · Accepted Answer · 2012-09-09 12:42:23Z

There is an awkward way of dumping input parameters :

create or replace function _tester(
  _txt text,
  _int int
) returns void
language 'plpgsql' as
$$
declare
  _out text = '';
  _rec record;
  _func_name text = '_tester';
begin

  for _rec in SELECT parameters.ordinal_position as _pos, parameters.parameter_name as _nm
  FROM information_schema.routines
  JOIN information_schema.parameters
  ON routines.specific_name=parameters.specific_name
  WHERE routines.routine_name = _func_name
  ORDER BY parameters.ordinal_position
  loop
    if _rec._pos = 1 then
       _out = _out || _rec._nm || ' = ' || $1::text || chr(10);
    elsif _rec._pos = 2 then
       _out = _out || _rec._nm || ' = ' || $2::text || chr(10);
    end if;
  end loop;

  raise notice '%', _out;
end;
$$;

select _tester('A','1');

NOTICE:  _txt = A
_int = 1

Notice that must add as many if/elsif as there are input parameters. Not sure if that part can be more concise.

Collectives™ on Stack Overflow

Postgresql: dump of input key/value pairs into a string variable in PL/pgSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related