CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Peter Porter
Peter Porter

Reputation: 397

Accessing object fields by name in PL/pgSQL object

I would like to access the fields/properties of an Object by a name passed in via text variable in a PL/SQL fnction. For instance, instead of:

obj.fieldA

I would like to do something like:

obj['fieldA']

Is there a way to accomplish this in PL/SQL? I am using Postgres, so perhaps I should say PL/pgSQL, but I hope that common syntax exists.

Upvotes: 1

Views: 694

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45835

What I know, the compiled language PL/SQL doesn't allow it. Usually this is a feature of dynamic languages. PLpgSQL doesn't support this feature too, but there are few workarounds:

  • dynamic SQL

    CREATE TABLE foo(a int, b varchar, c date);
INSERT INTO foo VALUES(10, 'Hello', CURRENT_DATE);

DO $$
DECLARE 
  foo_rec foo%ROWTYPE;
  colname text DEFAULT 'b';
  value text;
BEGIN
  SELECT * FROM foo INTO foo_rec;
  EXECUTE format('SELECT ($1).%I::text', colname) INTO value USING foo_rec;
  RAISE NOTICE '% = %', colname, value;
END;
$$;

  • Transformation to JSON and reading value from JSON value

    DO $$
DECLARE 
  foo_rec foo%ROWTYPE;
  colname text DEFAULT 'b';
  j json;
BEGIN
  SELECT * FROM foo INTO foo_rec;
  j := row_to_json(foo_rec);
  RAISE NOTICE '% = %', colname, j->>colname;
END;
$$;

Today using JSON is most comfortable way.

Upvotes: 2

Related Questions