Search code examples
sqlpostgresqlparametersprocedure

Postgres procedure won't recognize parameter when executed

I have the following procedure:

CREATE OR REPLACE PROCEDURE public.test_proc(
IN _intid integer DEFAULT 0)
LANGUAGE 'sql'
AS $BODY$
DO $$
DECLARE dteStart date;

BEGIN
    SELECT Startmonth FROM table1 WHERE id=_intid  INTO dteStart;
    RAISE NOTICE 'dteStart: %', dteStart;
END;
$$;
$BODY$;

When calling the above procedure using:

CALL test_proc(1)

I get the error:

column _intid does not exist.

When using positional parameter I get the error:

there is no parameter $1.

What am I doing wrong?

Solution

  • as stated in comment 1 and comment 2 use LANGUAGE plpgsql

    CREATE OR REPLACE PROCEDURE public.test_proc(
IN _intid integer DEFAULT 0)
LANGUAGE plpgsql
...