Why isn't this Postgres function correct?

Question

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.

Jonathan Hall · Accepted Answer · 2015-04-29 19:24:46Z

3

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

answered Apr 29, 2015 at 19:24

Jonathan Hall

80.5k19 gold badges162 silver badges206 bronze badges

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

4 Comments

MonkRocker Over a year ago

Now the function creates, but I get this error when executing:

select * from public.create_user('DerpDerp5678', 'password', 1); ERROR:  query has no destination for result data HINT:  If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT:  PL/pgSQL function create_user(text,text,integer) line 6 at SQL statement

MonkRocker Over a year ago

Marking this as the answer, but - doesn't matter at this point. I am sick of wrestling with crappy tools. Back to mySQL where mysterious, corner-case tasks like EDITING A FUNCTION aren't hidden away in dusty corners.

Jonathan Hall Over a year ago

You need a separate question for that error. Although it will probably be a duplicate.

Holger Jakobs Over a year ago

The error message of PostgreSQL is correct, informative and exhausting. You have done a query using SELECT, but not said what to do with the result. What else but an error message can you expect in this situation? But feel free to go back to some crappy database system.

Collectives™ on Stack Overflow

Why isn't this Postgres function correct?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related