1

In a function in plpgsql language and having:

  1. "col" CHARACTER VARYING = 'a'; (can be 'b')
  2. "rec" RECORD; Holding records from: (SELECT 1 AS "a", 2 AS "b")
  3. "res" INTEGER;

I need to reference the named column in "col", from "rec". So if "col" has 'b' I will reference "rec"."b", and then save its value into "res".

2
  • 1
    As always, version of PostgreSQL in use? Commented Jan 3, 2013 at 23:26
  • @ErwinBrandstetter not specified, so assume latest. Commented Jan 4, 2013 at 1:45

1 Answer 1

3

You cannot reference the columns of an anonymous record type by name in plpgsql. Even though you spell out column aliases in your example in the SELECT statement, those are just noise and discarded.

If you want to reference elements of a record type by name, you need to use a well-known type. Either create and use a type:

CREATE TYPE my_composite_type(a int, b int);

Or use the row type associated with any existing table. You can just write the table name as data type.

DECLARE
   rec my_composite_type;
...

Then you need a conditional statement or dynamic SQL to use the value of "col" as identifier.

Conditional statement:

IF col = 'a' THEN
   res := rec.a;
ELSIF col = 'b' THEN
   res := rec.b;
ELSE
   RAISE EXCEPTION 'Unexpected value in variable "col": %', col;
END IF;

For just two possible cases, that's the way to go.
Or dynamic SQL:

EXECUTE 'SELECT $1.' || col
INTO res
USING rec;

I don't see a problem here, but be wary of SQL injection with dynamic SQL. If col can hold arbitrary data, you need to escape it with quote_ident() or format()

Demo

Demonstrating the more powerful, but also trickier variant with dynamic SQL.

Quick & dirty way to create a (temporary!) known type for testing:

CREATE TEMP TABLE rec_ab(a int, b int);

Function:

CREATE OR REPLACE FUNCTION f_test()
  RETURNS integer AS
$func$
DECLARE
    col   text := 'a';  -- can be 'b'
    rec   rec_ab;
    res   int;
BEGIN
    rec := '(1, 2)'::rec_ab;

    EXECUTE 'SELECT $1.' || col
    INTO res
    USING rec;

    RETURN res;
END
$func$
  LANGUAGE plpgsql VOLATILE;

Call:

SELECT f_test();

Returns:

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.