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
– Balaji Perumal

2013-12-17 03:48:22 +00:00
Commented Dec 17, 2013 at 3:48 — Balaji Perumal
– Balaji Perumal, Commented Dec 17, 2013 at 3:48
@phpdeveloperbalaji no luck

ray
– ray

2013-12-17 03:51:44 +00:00
Commented Dec 17, 2013 at 3:51 — ray
– ray, Commented Dec 17, 2013 at 3:51
@phpdeveloperbalaji: "EXEC" is just nonsense.

Erwin Brandstetter
– Erwin Brandstetter

2013-12-17 04:02:04 +00:00
Commented Dec 17, 2013 at 4:02 — Erwin Brandstetter
– Erwin Brandstetter, Commented Dec 17, 2013 at 4:02

Community · Accepted Answer · 2017-05-23 12:03:44Z

1

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

edited May 23, 2017 at 12:03

CommunityBot

11 silver badge

answered Dec 17, 2013 at 4:00

Erwin Brandstetter

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

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

3 Comments

ray Over a year ago

Awesome man! Works like magic. Specially I like the shorter version.

ray Over a year ago

Don't know why but when I am trying to execute the alternative version it's throwing following error:

Erwin Brandstetter Over a year ago

@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.

Collectives™ on Stack Overflow

type EXECUTE does not exist in postgresql query

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related