Passing value of subquery in query in postgresql

Question

I'm trying to use the result of a subquery to be passed in the general query, but instead the value of the subquery is directly returned. In the below example, I have a table containing some numeric results, and a second table containing the formulas to be used in the query.

I have no problem querying the results directly by hardcoding the formulas:

SELECT      (col1 + col2)   AS "Result1", 
            col2            AS "Result2",                      
FROM   
myschema.results

But I'd like to use a subquery to fetch the formula from the formula table:

SELECT      (SELECT formula FROM myschema.formulas WHERE id = 'res1')    AS "Result1", 
            col2            AS "Result2",                      
FROM   
myschema.results

In the first query I correctly get something like

Result 1    Result 2
3           2

But when using the subquery I incorrectly get:

Result 1    Result 2
col1 + col2         2

How can I makesure the subquery result gets interpreted in the first query? I found some solutions using joins, but I'm not sure this is what I need and wonder if there is a simpler way to achieve this. Thanks for any help!

please update your questions with solution using join you have found. — Vao Tsun
– Vao Tsun, Commented Apr 9, 2017 at 19:25
You'll need something like this: dba.stackexchange.com/questions/33894/… — pozs
– pozs, Commented Apr 10, 2017 at 8:51

Evan Carroll · Accepted Answer · 2017-04-09 22:07:46Z

1

What you're asking for is dynamic SQL.

Something like this.

CREATE OR REPLACE FUNCTION foo()
RETURNS SETOF int AS
$$
  BEGIN
    RETURN QUERY EXECUTE 'SELECT ' || (SELECT 'x'::text) ||' FROM generate_series(1,10) AS gs(x);';
  END;
$$ LANGUAGE plpgsql;

answered Apr 9, 2017 at 22:07

Evan Carroll

1

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

Collectives™ on Stack Overflow

Passing value of subquery in query in 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