Returning column names by parameter postgresql

Question

Here's a simplified problem I have. I need to get certain values from a postgresql table using a function and passing parameters to it. Here's a simplified table and some values:

CREATE TABLE  testFoo (
  id text NOT NULL,
  "type" text,
  value1 float,
  value2 float,
  value3 float
)
WITH (
  OIDS=FALSE
);

INSERT INTO testFoo (id, "type", value1, value2, value3)
VALUES (1, 'testValue1', '0.11', '0.22', '0.33');
INSERT INTO testFoo (id, "type", value1, value2, value3)
VALUES (1, 'testValue2', '0.00', '0.00', '0.00');

I need to be able to fetch the values based on the 'type' column content. So for instance if the type = 'testValue2' I need to get to content of 'reading3' column... if it is 'testValue1' then I need 'reading2' out... Here's a function I came up:

DROP  FUNCTION getvalues(_values text[])
CREATE OR REPLACE FUNCTION getvalues(_values text[])
RETURNS TABLE (id text, type text, value float) AS
$BODY$ 
BEGIN
 EXECUTE 'SELECT t.id, t.type,
  CASE 
     WHEN t.type = '|| _values[1] ||' THEN '|| _values[2] ||'
  END AS value
FROM testFoo t';
END;
$BODY$
LANGUAGE plpgsql;

SELECT * FROM getvalues(ARRAY['testValue2','"reading3"']);

However it gives me the follwoing error:

ERROR:  column "testvalue2" does not exist
LINE 3:      WHEN t.type = testValue2 THEN "reading3"
                           ^
QUERY:  SELECT t.id, t.type,
  CASE 
     WHEN t.type = testValue2 THEN "reading3"
  END AS value
FROM testFoo t
CONTEXT:  PL/pgSQL function getvalues(text[]) line 3 at EXECUTE
********** Error **********

ERROR: column "testvalue2" does not exist

I've tried format() and loads of other options unsuccessfully... Could you kindly look in this? Thanks.

Patrick · Accepted Answer · 2017-03-07 08:39:02Z

2

You should use the format() function with the appropriate format specifiers, in particular %I for identifiers (column name, in this case) and %L for string literals that need quoting. Also, you need to return the actual data from the function.

CREATE OR REPLACE FUNCTION getvalues(_values text[])
RETURNS TABLE (id text, type text, value float) AS
$BODY$ 
BEGIN
  RETURN QUERY EXECUTE format('SELECT id, "type",
    CASE WHEN "type" = %L THEN %I
    END AS value
    FROM testFoo', _values[1], _values[2]);
END;
$BODY$
LANGUAGE plpgsql;

answered Mar 7, 2017 at 8:39

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

Returning column names by parameter postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related