CODEWITHSUNDEEP
postgresqlsubquery
sc28
sc28

Reputation: 1213

Passing value of subquery in query in postgresql

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!

Upvotes: 0

Views: 955

Answers (1)

Evan Carroll
Evan Carroll

Reputation: 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;

Upvotes: 1

Related Questions