CODEWITHSUNDEEP
postgresqlpsql
MonkRocker
MonkRocker

Reputation: 309

Why isn't this Postgres function correct?

CREATE OR REPLACE FUNCTION public.create_user(_name TEXT, _pass TEXT, _iconid INTEGER)
RETURNS user_info
LANGUAGE sql
AS $$
DECLARE
    _newid INTEGER;
BEGIN
    INSERT INTO users(name, password, iconid) VALUES (_name, crypt(_pass, gen_salt('bf')), _iconid);
    SELECT _newid = currval(pg_get_serial_sequence('users', 'id'));


    SELECT u.id, u.name, u.rating, t.name, i.path, i.name FROM users u
    LEFT OUTER JOIN usertypes t ON t.id = u.typeid
    LEFT OUTER JOIN usericons i ON i.id = u.iconid
    WHERE u.id = _newid;
END
$$;

I get: 

ERROR: syntax error at or near "INTEGER"
Line 6:    _newid INTEGER;
                  ^

I am sure there's something I'm missing but it's been really frustrating so far because the tools are substandard compared to almost EVERY other major db - mysql, sql server, oracle, etc.

Upvotes: 0

Views: 81

Answers (1)

Jonathan Hall
Jonathan Hall

Reputation: 79604

Why isn't this Postgres function correct?

Because it doesn't contain valid SQL. It appears to contain plpgsql code. Try changing the language:

LANGUAGE plpgsql

Upvotes: 3

Related Questions