CODEWITHSUNDEEP
postgresqlloopsstored-proceduresplpgsqldynamic-sql
chaitanya89
chaitanya89

Reputation: 847

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

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?

Upvotes: 0

Views: 17227

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

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.

Upvotes: 2

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

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

Upvotes: -1

Related Questions