CODEWITHSUNDEEP
sqlpostgresql
doe
doe

Reputation: 25

how to create function in postgres

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

Upvotes: -1

Views: 303

Answers (1)

user330315
user330315

Reputation:

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));

Upvotes: 2

Related Questions