Syntax error at or near "FOR" while using "execute format" in a function

Question

This function compiled successfully:

CREATE OR REPLACE FUNCTION FieldValidations1(tbl_name varchar(35),col_name varchar(25), error_flag varchar(3))
RETURNS void AS $$
declare
    cust_rec RECORD;
BEGIN
    execute format($sel$

    FOR cust_rec IN SELECT %I FROM %s 
    LOOP
    RAISE NOTICE 'inside loop';
    END LOOP;

    $sel$,
    col_name,tbl_name);
END;
$$ LANGUAGE plpgsql;

But while calling the function,

select FieldValidations1('raw_tbl1','col1','gg');

the error appears like this

ERROR:  syntax error at or near "FOR"
LINE 3: FOR cust_rec IN SELECT col1 FROM raw_tbl1
    ^
QUERY:

FOR cust_rec IN SELECT col1 FROM raw_tbl1
LOOP
RAISE NOTICE 'inside loop';
END LOOP;

CONTEXT:  PL/pgSQL function "fieldvalidations1" line 6 at EXECUTE statement

Can anyone explain what's wrong?

Erwin Brandstetter · Accepted Answer · 2014-08-19 21:26:04Z

2

You must be aware that plpgsql functions are only tested on a superficial syntactical level in CREATE FUNCTION. It does not find all possible errors, it does not try to evaluate functions.

Would work like this:

CREATE OR REPLACE FUNCTION field_validations2(tbl_name text
                                            , col_name text, error_flag text)
  RETURNS void AS
$func$
DECLARE
   cust_rec RECORD;
BEGIN
    FOR cust_rec IN
        EXECUTE format('SELECT %I FROM %I', col_name, tbl_name)
    LOOP
      RAISE NOTICE 'inside loop';
    END LOOP;
END
$func$ LANGUAGE plpgsql;

The proper syntax for a FOR-IN-EXECUTE statement can be found in the manual.

Also fixed a couple of other things. text instead of varchar(n) is just a friendly advice.

answered Aug 19, 2014 at 21:26

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

Comments

Clodoaldo Neto · Accepted Answer · 2014-08-19 12:56:38Z

-1

It is not possible to execute plpgsql. It must be plain SQL.

answered Aug 19, 2014 at 12:56

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

Syntax error at or near "FOR" while using "execute format" in a function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related