0

I have an sql statement stored in a PostgreSQL 9.4 database. Can I use the stored statement, select it within another statement and use the result set as a table?

For example: In the table "pc_preset" there are multiple records with SQL statements stored in the field "sql_rule". I can query them:

SELECT
   sql_rule
FROM
   mc_preset
WHERE
   id_preset = 1

This gives me the stored statement as a STRING, for example:

SELECT
   user_id,
   firstname,
   lastname,
   birthdate,
   birthplace_id
FROM
   user
WHERE
   gender_id = 1

Now I want to create another statement and use the result set of the stored statement as alias table and join it with other tables.

SELECT *
FROM
   RESULT_SET_OF_SQL_RULE AS "table1"
   LEFT JOIN table2 ON table2.birthplace_id = table1.birthplace_id

In another example I want the result set to be used in a subselect.

SELECT *
FROM
   table2
WHERE
   birthplace_id IN (SELECT birthplace_id
                     FROM RESULT_SET_OF_SQL_RULE
                     WHERE birthdate < '2000-01-01'
                    )

I do not want to work with DB views as the stored statement can be modified by (admin-)users in an application and needs to be stored in a DB table therefore.

On the basis of How can I use an SQL statement stored in a table as part of another statement? I created a PostgreSQL function but I am not able to modify it to my needs.

CREATE OR REPLACE FUNCTION exec_mc_preset_sql_rule_get_result_set(preset_id integer)
 RETURNS ??
 LANGUAGE plpgsql
AS $function$
declare
    result_set integer;
    stmt character varying;
begin
    SELECT sql_rule into stmt
    FROM mc_preset
    WHERE id_preset = preset_id;
    
    execute immediate stmt
    ??  into result_set;
    
    return result_set;
end
$function$
;

If it is not possible to give the complete result set but an array of the values of one column only, it would be great to specifiy the field that I want to get back in a second parameter.

  • How do I have to write the function
  • How do I use the function within my statements?

Thank you very much.

Aljoscha
  • 21
  • 4
  • Unrelated to your problem, but: Postgres 9.4 is [no longer supported](https://www.postgresql.org/support/versioning/) you should plan an upgrade as soon as possible. –  Nov 02 '22 at 11:19

1 Answers1

0

Basically you can modify your statement and then use execute immidiate. But this can lead to undefined errors as it is not guranteed that table1 has birthplace_id.

CREATE OR REPLACE FUNCTION exec_mc_preset_sql_rule_get_result_set(preset_id integer)
 RETURNS ??
 LANGUAGE plpgsql
AS $function$
declare
    result_set integer;
    stmt character varying;
    stmt1 character varying;
begin
    SELECT sql_rule into stmt
    FROM mc_preset
    WHERE id_preset = preset_id;
    
    --you can add some logic in your stored procedure to understand what to do next
    --and add modification for stored statement before execute immidiate
    stmt1 := 'SELECT * FROM (' || stmt || ') AS table1 LEFT JOIN table2 ON 
             table2.birthplace_id = table1.birthplace_id';
    
    execute immediate stmt
    ??  into result_set;
    
    return result_set;
end
$function$
;
Martins
  • 39
  • 5
  • Thank you. Unfortunately the function is not correct yet. Please note the questionmarks that I inserted. Also I need the stmt1 in another statment that calls this function. – Aljoscha Nov 03 '22 at 07:29
  • More about - how to use execute immidiate - you can check - https://stackoverflow.com/questions/17547666/execute-into-using-statement-in-pl-pgsql-cant-execute-into-a-record – Martins Nov 03 '22 at 10:25
  • If you need a loop or cursor - you can check this - https://stackoverflow.com/questions/35159983/pl-sql-execute-immediate-into-a-cursor – Martins Nov 03 '22 at 10:29