plpgsql CREATE FUNCTION syntax error at or near 'CREATE'

Question

Can't seem to get this function to Create, even when I empty out all the DECLARE block and the BEGIN-END block and just try to return a string. Can anyone help me with what I've done wrong here?

Trying to execute this in pgAdminIII (Ubuntu):

CREATE OR REPLACE FUNCTION split_country()
    RETURNS text as $$

DECLARE 
titlecsv text;
arCountries text[];
country_cursor CURSOR
FOR SELECT DISTINCT country 
FROM festfriend.films;
BEGIN
    OPEN country_cursor;
    LOOP
        FETCH NEXT FROM country_cursor INTO titlecsv;
        EXIT WHEN NOT FOUND;

        SELECT regexp_split_to_array(titlecsv, ',') INTO arCountries;
        RAISE NOTICE '%1', arCountries;

        INSERT INTO festfriend.country (name, acronym)
        SELECT trim(both ' ' from a.column2), upper(left(trim(both ' ' from a.column2), 3))
        FROM   unnest((SELECT arCountries)::text[]) WITH ORDINALITY a(column2)
        WHERE (SELECT COUNT(id) FROM festfriend.country WHERE name = trim(both ' ' from a.column2)) = 0
        AND char_length(trim(both ' ' from a.column2)) > 0;

    END LOOP;

    CLOSE country_cursor;

    RETURN 'Split Countries, yo!';
END; $$

LANGUAGE 'plpgsql';

Getting the following error:

ERROR:  syntax error at or near "CREATE"
LINE 1: CREATE OR REPLACE FUNCTION split_country()
        ^

********** Error **********

ERROR: syntax error at or near "CREATE"
SQL state: 42601
Character: 1

You are doing some wrong - it is working without any problem on my comp. Probably you try to send more then one statement to server and you are missing semicolon. — Pavel Stehule
– Pavel Stehule, Commented Jun 15, 2016 at 5:50

Ezequiel Tolnay · Accepted Answer · 2016-06-15 09:31:23Z

6

If you're creating the function via the "New Function..." screen, tab "Code", then you have to enter only the part between the two $$ (i.e. starting with DECLARE and ending with END;). The parameters, return type, etc. are defined in the other tabs.

answered Jun 15, 2016 at 9:31

Ezequiel Tolnay

4,5821 gold badge21 silver badges28 bronze badges

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

Comments

Carl · Accepted Answer · 2019-02-13 13:52:36Z

2

Your file may be encoded in UTF-8 with a byte-order mark (BOM). This invisible data will appear in the file just before your CREATE and is enough to confuse Postgres and give this misleading error message.

Try opening the file in Notepad++ and selecting Encode in UTF-8 under the Encoding menu.

answered Feb 13, 2019 at 13:52

Carl

1,00811 silver badges16 bronze badges

Collectives™ on Stack Overflow

plpgsql CREATE FUNCTION syntax error at or near 'CREATE'

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related