"Execute does not exist" when writing Postgres Function

Question

I'm working on a function, which is supposed to look up for some tables if there was written at least one entry yesterday. In any case, TRUE or FALSE should be written into another table:

  CREATE OR REPLACE FUNCTION SANDBOX.MY_FUNCTION(MY_TABLE regclass, DATE_DIFF INTEGER)
    RETURNS void AS $$
 DECLARE
RESULT BOOLEAN;
 BEGIN
      RESULT :=
      EXECUTE 'EXISTS
      (SELECT DATE FROM ' || MY_TABLE ||
       ' WHERE DATE = CURRENT_DATE -DATE_DIFF
       LIMIT 1)';
      INSERT INTO SANDBOX.UPDATED_TODAY VALUES (MY_TABLE, CURRENT_DATE, RESULT);
    END;
    $$ LANGUAGE plpgsql;

However, I'm getting

[42704]ERROR: type "execute does not exist"

This is my function call:

SELECT SANDBOX.MY_FUNCTION('datawarehouse_a.table_2', 1);

Can anybody help? Thanks in advance! :-)

If you're courious about the 2nd parameter: For some tables, I'm looking if there was an entry not yesterday, but 2 days ago.

404 · Accepted Answer · 2018-11-29 13:46:07Z

2

EXECUTE does not return a result like that. You need to EXECUTE INTO to get the result into a variable:

DO $$
DECLARE
        result BOOLEAN;
BEGIN
        EXECUTE 'SELECT EXISTS (SELECT 1 FROM generate_series(1, 10))'
        INTO result;

        RAISE NOTICE '%', result;
END $$

Prints: NOTICE: t

answered Nov 29, 2018 at 13:46

404

8,7622 gold badges34 silver badges53 bronze badges

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

Collectives™ on Stack Overflow

"Execute does not exist" when writing Postgres Function

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