how to create function in postgres

Question

how to create function in PostgreSQL am using below code

create table sample(id int primary key)
create table sample2(id int primary key)


create view sampleall
As 
select id from sample
union
select id from sample2

while creating below function for above tables

Create FUNCTION CheckFunction (ID INT)
RETURNS BIT AS
$$
BEGIN

    IF EXISTS (SELECT 1 FROM sampleall WHERE id = @ID)
    BEGIN

        RETURN 1
    END




    RETURN 0

END
  $$
 LANGUAGE plpgsql;

CREATE TABLE sample3 (ID INT NOT NULL CONSTRAINT CHK_ID CHECK (dbo.CheckFunction(ID) = 1))

and getting below error

ERROR:  syntax error at or near "BEGIN"
LINE 8:     BEGIN
            ^
********** Error **********

ERROR: syntax error at or near "BEGIN"
SQL state: 42601
Character: 132

please help to solve the issue

Add a THEN...

jarlh
– jarlh

2019-01-02 13:02:30 +00:00
Commented Jan 2, 2019 at 13:02 — jarlh
– jarlh, Commented Jan 2, 2019 at 13:02

user330315 · Accepted Answer · 2019-01-02 13:24:44Z

2

As documented in the manual an IF requires a THEN. Additionally, parameters cannot be prefixed with a @. To avoid a name clash between a parameter name and a column name you should use a different name for the parameter. A common approach is to prefix parameters with p_

However your function is needlessly complex and can be simplified substantially if you return a proper boolean rather than a bit:

Create FUNCTION check_function (p_ID INT)
RETURNS boolean AS
$$
  select exists (SELECT 1 FROM sampleall WHERE id = p_id);
$$
LANGUAGE sql;

Your check constraint can then be simplified to:

CREATE TABLE sample3 (ID INT NOT NULL CONSTRAINT CHK_ID CHECK (check_Function(ID));

answered Jan 2, 2019 at 13:24

user330315

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

Collectives™ on Stack Overflow

how to create function in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related