Postgresql – how to execute sql inside a column in postgres

plpgsqlpostgresqlpostgresql-9.5

I am trying to execute SQL statements that exist within a column of a database table.

The records of this table has 'tests' in each row.

sid  name     sql
1    test1    SELECT source_system_name, Count(*), CASE WHEN Count(*) BETWEEN 500 AND 2000 THEN true ELSE false END AS valid FROM prod.listing WHERE source_record_status = 'active' AND modified_on BETWEEN %L AND(%L - interval '24 hours') GROUP BY source_system_name;
2    test2    SELECT source_system_name, ....;

the output from the SQL inside sql column generates 3 columns – source_system_name, count, and valid.
Whenever function validation() is run I want to output the result – when valid = false

I wrote code for the innermost logic i.e. whenever there is false in value column.

CREATE OR REPLACE FUNCTION validation()
   RETURNS text AS $$
DECLARE
 show_log TEXT DEFAULT '';
 rec_val RECORD;
 cur_val CURSOR
  FOR SELECT   source_system_name,
         Count(*),
         CASE
                  WHEN Count(*) BETWEEN 500 AND 2000 THEN CAST(true AS BOOLEAN)
                  ELSE CAST(false AS BOOLEAN)
         END AS valid
      FROM     prod.listing
      WHERE    source_record_status = 'active'
      AND      modified_on BETWEEN '2001-06-29'::TIMESTAMP AND     (
                        '2017-06-29'::TIMESTAMP - interval '24 hours')
      GROUP BY source_system_name;
BEGIN
   -- Open the cursor
   OPEN cur_val;

   LOOP
    -- fetch row into the rec_val
      FETCH cur_val INTO rec_val;
    -- exit when no more row to fetch
      EXIT WHEN NOT FOUND;

    -- build the output
      IF rec_val.valid =  false THEN
         show_log := rec_val.source_system_name;
      END IF;
   END LOOP;

   -- Close the cursor
   CLOSE cur_val;

   RETURN show_log;
END; $$

LANGUAGE plpgsql;

I am a bit confused as in how to use this logic for all the statements in sql column.

Best Answer

You don't put the condition inside the procedure, you call a procedure only when the condition is true. The calling context should look something like

SELECT id, name, rs.*
FROM tests AS t
CROSS JOIN LATERAL myDynamic3ColExec(sql) AS rs
WHERE EXISTS (
      SELECT 1
      FROM     prod.listing
      WHERE    t.name = listing.source_system_name
      AND      source_record_status = 'active'
      AND      modified_on BETWEEN '2001-06-29'::TIMESTAMP AND                        ('2017-06-29'::TIMESTAMP - interval '24 hours')
      GROUP BY source_system_name
      HAVING Count(*) BETWEEN 500 AND 2000
);

Now you have to create a type, and create a function that returns the type given a sql statement as text.

CREATE TYPE mytype AS ( sid int, name text, sql text );

CREATE FUNCTION myDynamic3ColExec(sql text)
RETURNS setof mytype
AS $$
  RETURN QUERY EXECUTE sql;
$$ LANGUAGE plpgsql
VOLATILE;
Related Question