CODEWITHSUNDEEP
postgresqlplpgsql
taivoe
taivoe

Reputation: 31

plpgsql CREATE FUNCTION syntax error at or near 'CREATE'

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

Upvotes: 2

Views: 5150

Answers (2)

Carl
Carl

Reputation: 998

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.

enter image description here

Upvotes: 2

Ezequiel Tolnay
Ezequiel Tolnay

Reputation: 4572

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.

Upvotes: 6

Related Questions