How to use a Function Parameter in a Cursor that's incorporated with Dynamic SQL in Postgres Functions?

Question

Created this Postgres Function which is working fine, but the actual requirement is to pass the input parameter in the function to the Cursor which uses the dynamic SQL as follows,

The below is the Function

CREATE OR REPLACE FUNCTION ssp2_pcat.find_shift_dates (date_to_find date)
  RETURNS void
  LANGUAGE 'plpgsql'

  COST 100
  VOLATILE 
AS $BODY$

DECLARE
C1 CURSOR FOR
SELECT TABLE_NAME, 'SELECT COUNT(*) FROM ' || TABLE_NAME || ' WHERE ' || 
COLUMN_NAME || ' = '||
'CASE WHEN ' || COLUMN_NAME || ' LIKE ' || '''%START%'''||' THEN 
 date_to_find ELSE date_to_find-1 END;' SQL_TEXT 
FROM (
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM RESET_DATES WHERE RESET_IT = 
'Y') AND
UPPER(DATA_TYPE) = 'DATE' 
AND (COLUMN_NAME LIKE '%START%' OR COLUMN_NAME LIKE '%END%')
AND (COLUMN_NAME NOT LIKE '%TEST%' 
AND COLUMN_NAME NOT LIKE '%PCAT%' 
AND COLUMN_NAME NOT LIKE '%ORDER%' 
AND COLUMN_NAME NOT LIKE '%SEASON%' 
AND COLUMN_NAME NOT LIKE '%_AT')
ORDER BY 1, 2) A;

END_COUNT     INTEGER := 0;
START_COUNT   INTEGER := 0;
TABLENAME     VARCHAR(32) := 'ALFU';
l_start       TIMESTAMP;
l_end         TIMESTAMP;
Time_Taken    VARCHAR(20);

BEGIN
  l_start  := clock_timestamp();
  DELETE FROM SHIFT_DATES_COUNT;
  FOR I IN C1 LOOP
    IF I.TABLE_NAME <> TABLENAME THEN
      INSERT INTO SHIFT_DATES_COUNT VALUES (TABLENAME, START_COUNT, 
      END_COUNT, current_timestamp::timestamp(0));
      TABLENAME := I.TABLE_NAME;
      END_COUNT := 0;
      START_COUNT := 0;
    END IF;
    IF STRPOS(I.SQL_TEXT, 'END') > 0 THEN
     EXECUTE I.SQL_TEXT INTO END_COUNT;
     RAISE NOTICE '% ', ('END: ' || I.SQL_TEXT); 
    ELSE
     EXECUTE I.SQL_TEXT INTO START_COUNT;
     RAISE NOTICE '% ', ('START: ' || I.SQL_TEXT); 
    END IF;
  END LOOP;
 INSERT INTO SHIFT_DATES_COUNT VALUES (TABLENAME, START_COUNT, END_COUNT, 
 current_timestamp::timestamp(0));
 RAISE NOTICE '% ', ('INSERT INTO SHIFT_DATES_COUNT Done...'); 
 l_end  := clock_timestamp();
 Time_Taken := (l_end-l_start); 

 RAISE NOTICE '% ', ('FIND_SHIFT_DATES Took: ' || Time_Taken );
END;

$BODY$;

Please let me know how can I use the date_to_find input parameter in the Dynamic SQL in the Cursor in the above Function.

Anthony Sotolongo · Accepted Answer · 2019-02-11 22:32:09Z

2

You can use unbound cursor, clause fetch to get data from cursor, and exit when not found to finish, like:

CREATE OR REPLACE  FUNCTION example (p_name text)  RETURNS void LANGUAGE 'plpgsql' AS $$

DECLARE
C1 refcursor; 
res record;
BEGIN
OPEN c1 FOR EXECUTE 'SELECT * FROM pg_database WHERE datname like ''%'||p_name||'%''';


 LOOP
FETCH c1 INTO res;
EXIT WHEN not found;
raise notice 'value datname: %',res.datname;
END LOOP;

CLOSE c1;
RETURN;
END;   $$;


--in my case
select example ('test')
NOTICE:  value datname: test
NOTICE:  value datname: test_msmov
NOTICE:  value datname: test_resources
NOTICE:  value datname: test_load_table
NOTICE:  value datname: test_resources2
Total query runtime: 63 msec
1 row retrieved.

answered Feb 11, 2019 at 22:32

Anthony Sotolongo

1,6882 gold badges13 silver badges23 bronze badges

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

Comments

2 revs · Accepted Answer · 2019-02-10 19:28:35Z

0

You can use EXECUTE clause for open cursor, see the documentation of PostgreSQL https://www.postgresql.org/docs/10/plpgsql-cursors.html#PLPGSQL-CURSOR-OPENING Example:

OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;

edited Feb 10, 2019 at 19:28

community wiki

2 revs
Anthony Sotolongo

1 Comment

user10531062 Over a year ago

I'm still trying to figure out on how I can use the above logic in my function after going through the attached article, can you please throw some light on it ?

Collectives™ on Stack Overflow

How to use a Function Parameter in a Cursor that's incorporated with Dynamic SQL in Postgres Functions?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related