1

I'm trying to create a function, like so:

CREATE FUNCTION RETURNONE(DATE)
BEGIN
  RETURN 1;
END

However, when I run this in psql 9.5 I get the following error:

ERROR:  syntax error at or near "BEGIN"
LINE 2: BEGIN
        ^
END

I did see this other StackOverflow thread with a reminiscent problem. Per the second answer, I re-encoded my code in UTF 8, which did nothing. This is my first ever SQL function, so I'm sure I'm missing something painfully obvious. Let me know what!

Alex V
  • 3,416
  • 2
  • 33
  • 52
  • 3
    Have you read the [CREATE FUNCTION documentation](https://www.postgresql.org/docs/current/sql-createfunction.html)? – mu is too short Feb 27 '19 at 01:20
  • @muistooshort Honestly, I read that exact page. I don't know why, but I just assumed all that LANGUAGE and $func$ stuff wasn't necessary. In retrospect, rereading it, it's obvious. Thanks! – Alex V Feb 27 '19 at 01:32
  • 1
    About that "`$func$` stuff": https://stackoverflow.com/a/12172353/939860 – Erwin Brandstetter Feb 27 '19 at 02:19

1 Answers1

1

You omitted some essential syntax elements:

CREATE FUNCTION returnone(date)
  RETURNS integer
  LANGUAGE plpgsql AS
$func$
BEGIN
  RETURN 1;
END
$func$;

The manual about CREATE FUNCTION.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Wow, this did it. I read that exact document twice, don't know how I came out so wrong. Thanks a billion! – Alex V Feb 27 '19 at 01:33
  • @seisvelas Unrelated, but: you don't need PL/pgSQL for something like that. A simple `language sql` function would be more efficient if you don't have any procedural code (`if`, `while`, or similar constructs) in your function –  Feb 27 '19 at 06:56
  • @a_horse_with_no_name Interesting! I'm a complete beginner, how would this work using `language sql`? – Alex V Mar 04 '19 at 17:20
  • @seisvelas: essentially removing `begin` and `end` and replace `return` with `select`. See here: https://rextester.com/KITLW87371 –  Mar 04 '19 at 17:23