Question 311387 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – I'm trying to create a SQL procedure. ERROR: syntax error (example position: "SELECT")

CREATE PROCEDURE test()
BEGIN
    SELECT  'hello, I am a Procedure' AS hi;
END

Error:

ERROR: syntax error (example position: "SELECT")
LINE 3: SELECT 'hello, I am a Procedure' AS hi;
          ^

ERROR: syntax error (example position: "SELECT")
SQL state: 42601
Character: 32

I’m trying to create a procedure but nothing works and I don’t know what to do, I’ve surfed the Internet. I use sql Scrapbook (eclipse (java)), I tried to run the query through pgAdmin 4, it didn’t work either.

Tags:

2

Answers


  1. 0

    For Postgres, the CREATE PROCEDURE documentation says you can either use the AS $$ ... $$ or the BEGIN ATOMIC ... END syntax:

    CREATE PROCEDURE test()
LANGUAGE SQL
AS $$
    SELECT  'hello, I am a Procedure' AS hi;
$$;

CREATE OR REPLACE PROCEDURE test()
LANGUAGE SQL
BEGIN ATOMIC
    SELECT  'hello, I am a Procedure' AS hi;
END;

    For SQL Server, a procedure without parameters is declared without parentheses, and don’t forget AS:

    CREATE PROCEDURE test1
AS BEGIN
    SELECT  'hello, I am a Procedure' AS hi;
END;
    Login or Signup to reply.
  2. 0

    You attempt to return a string. A PROCEDURE can (as of Postgres 16) only return a single row by way of using INOUT or OUT parameters. So your attempt translates to:

    CREATE OR REPLACE PROCEDURE test(INOUT hi text = NULL)
  LANGUAGE sql AS
$proc$
SELECT 'hello, I am a Procedure';
$proc$;

    Or the "SQL standard" variant in Postgres 14 or later:

    CREATE OR REPLACE PROCEDURE test(INOUT hi text = NULL)
  LANGUAGE sql
BEGIN ATOMIC
   SELECT 'hello, I am a Procedure';
END;

    Or the short form for this simple case:

    CREATE OR REPLACE PROCEDURE test(INOUT hi text = NULL)
  LANGUAGE sql
RETURN 'hello, I am a Procedure';

    And you have to CALL a procedure to execute:

    CALL test();

    We can omit the INOUT parameter in the call because we declared NULL as default value (as we should).

    See:

    I suspect you just fell for the widespread misnomer "stored procedure" and really want a FUNCTION.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search