By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,725 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

How to create a bool function for the creation of check constraint in plpgsql?

P: 2
am trying to create a boolean function in plpgsql for using it in check constraint .
a table 'entity_groups' having columns as - id ,name ,parent_id.
check constrained is to be placed on 'parent_id' and the data in parent_id should be in "entity_groups"."id".
"entity_groups"."id" should not be equal to corresponding "parent_id".
here "parent_id" is the parent of the "entity_groups"."id".

Expand|Select|Wrap|Line Numbers
  1.  
  2.  CREATE FUNCTION centr.check_valid_parent_id(varchar(36)) RETURNS boolean AS $$
  3.  DECLARE
  4.  entgrid ALIAS FOR $1;
  5.  egid record;
  6.  ancestors varchar(36)[];
  7.  count integer;
  8.  BEGIN
  9.     IF entgrid IS NULL THEN 
  10.     RETURN 't';
  11.        IF entgrid IS NOT NULL 
  12.         THEN 
  13.         count := 0;
  14.              FOR entgrid IN EXECUTE centr.parent_id()
  15.              LOOP
  16.            IF entgrid IN ancestors 
  17.             THEN RETURN 'f';    
  18.             ELSE
  19.            ancestors << entgrid;
  20.            entgrid := PERFORM SELECT parent_id FROM centr.entity_groups WHERE entity_groups.id = entgrid;
  21.               RETURN 't';
  22.            ENDIF;
  23.              count = count + 1;
  24.              END LOOP;
  25.          ENDIF;
  26.         ENDIF;
  27.     RETURN 'f';
  28.  END;
  29.  $$ LANGUAGE 'plpgsql';
  30.  
  31.  
  32. CREATE FUNCTION "centr"."parent_id"() RETURNS SETOF varchar(36)
  33.     AS $BODY$
  34.  select id from centr.entity_groups;
  35. $BODY$
  36.     LANGUAGE SQL;
  37.  
  38.  
when trying to execute getting an error as

ERROR: "$1" is declared CONSTANT
CONTEXT: compilation of PL/pgSQL function "check_valid_parent_id" near line 13

please help me out ..
Nov 17 '10 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 700
Well you can't do that with input (IN) parameters
FOR entgrid IN EXECUTE centr.parent_id()
You can't change IN parameters value
Nov 18 '10 #2

P: 2
FOR entgrid IN EXECUTE centr.parent_id()

but i tried all conditions rski,which results in different errors what all i want is to check is whether the 'entgrid' exists in the values obtained from function "centr.parent_id()"

please help me out. I hope it may be possible with PERFORM , PREPARE,or EXECUTE .am also working on the same issue.

thanx a lot and waiting for your reply rski.
Nov 19 '10 #3

Expert 100+
P: 700
First of all, this look strange
IF entgrid IS NULL THEN
RETURN 't';
IF entgrid IS NOT NULL
Shoudn't you write something like that
Expand|Select|Wrap|Line Numbers
  1.   IF entgrid IS NULL THEN 
  2.      RETURN 't';
  3.   ELSE 
  4.       ...
  5.  
next ...

What is the value of ancestors variable when you first call that
IF entgrid IN ancestors
If id is unique you can resolve the problem with simple foreign key constraint and check constraint

Expand|Select|Wrap|Line Numbers
  1. create table entity_groups(
  2. id <type> unique check (id<>parent_id)
  3. ,name <type>
  4. ,parent_id <type> references entity_groups(id));
  5.  
Nov 19 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.