type EXECUTE does not exist in postgresql query

Question

I am trying to create following function. Function has been created successfully but when trying to call it's throwing error

type EXECUTE does not exist!

CREATE OR REPLACE FUNCTION exa(ids text, length integer, fields text)  returns text AS
$BODY$
DECLARE
    chars INT[] := string_to_array(ids, ',');
    result text := '';
    i integer := 0;
    temp text := '';
BEGIN

    for i in 1..length loop
        temp := EXECUTE 'SELECT ' || fields || ' from user_index where userid=' || chars[i];
            result := result || temp;
        IF i < length THEN
            result := result || ',';    
        END IF;
    end loop;
    return result;
END
$BODY$ language plpgsql;

Try EXEC instead of EXECUTE – Balaji Perumal Dec 17 '13 at 03:48 — Balaji Perumal, Dec 17 '13 at 03:48
@phpdeveloperbalaji: "EXEC" is just nonsense. – Erwin Brandstetter Dec 17 '13 at 04:02 — Erwin Brandstetter, Dec 17 '13 at 04:02

score 1 · Accepted Answer · edited May 23 '17 at 12:03

Assuming fields is supposed to be a column name.

CREATE OR REPLACE FUNCTION exa(ids text, length integer, fields text
                              ,OUT result text) AS
$BODY$
DECLARE
    _chars INT[] := string_to_array(ids, ',');
    i integer    := 0;
    _temp text   := '';
    _arr  text[];
BEGIN
    FOR i IN 1..length loop
        EXECUTE 'SELECT ' || fields || ' FROM user_index WHERE userid = chars[i]'
        INTO temp;
        _arr := _arr || temp;
    END LOOP;
    result := array_to_string(result, ',');

    RETURN;
END
$BODY$ language plpgsql;

But really just:

CREATE OR REPLACE FUNCTION exa(ids text, fields text, OUT result text) AS
$BODY$
BEGIN
   EXECUTE
   format($$SELECT string_agg(%I, ',')
            FROM   user_index
            JOIN   unnest(string_to_array($1, ',')::int[]) i(userid)
                                                      USING (userid)$$
         ,fields)
   USING ids
   INTO  result;
END
$BODY$ language plpgsql;

Call:

SELECT exa('10,11,12', 'kat')

And be sure to avoid SQL injection using format() with %I or some other sane method. Try the search tools. Many similar answers here on SO - like this one (with more explanation and links):
INSERT with dynamic table name in trigger function

Awesome man! Works like magic. Specially I like the shorter version. — ray, Dec 17 '13 at 04:06
Don't know why but when I am trying to execute the alternative version it's throwing following error: — ray, Dec 17 '13 at 05:40
@ray: I found your error msg. in the rejected edits. I had two errors in the code of the second function. Fixed and tested now. — Erwin Brandstetter, Dec 17 '13 at 15:46

type EXECUTE does not exist in postgresql query

1 Answers1