Skip site navigation (1) Skip section navigation (2)

Sql Functions

From: Randy Neumann <Randy_Neumann(at)centralref(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Sql Functions
Date: 2002-08-09 13:36:26
Message-ID: 200208091341.HAA28452@mail.simn.com (view raw or flat)
Thread:
Lists: pgsql-novice
Can anyone see what the problem is with this sql function?  When I run the 
query outside the function I get the expected result.  It seems that SQL 
functions do not like the keyword 'OR'.  Does anyone know if this is a known 
bug?

Thanks, Randy

Here are the tables:

create table T1(
    t1c1    integer primary key,
    t1c1d varchar(20)
);
create table T2(
    t2c1    char(20) primary key,
    t2c1d varchar(200)
);
create table T3(
    t3c1     char(20) primary key,
    t3c1d varchar(200)
);
create table T4(
    t4c1     char(20) references T3,
    t4c2 char(20) references T2,
    t4c3   integer references T1
);
create table T5(
    t5c1     char(20) references T3,
    t5c2 char(20) references T3,
    t5c3 integer references T1
);
create table T6(
    t6c1   char(10),
    t6c2 char(20) references T3
);

Sample Data:
T1:
 t1c1      | t1c1d 
----------+----------------------
          0 | T1R0
          1 | T1R1
          2 | T1R2

T2:
    t2c1    |  t2c1d                                        
-----------+---------------------------------------------------------------------------------------------------
 123        | stuff
 456        | stuff
 789        | stuff
 0ab        | stuff
 cde        | stuff

T3:
 t3c1  |                               t3c1d                               
-------+-----------------------------------------------------------------------------
 a1     | stuff
 b2     | stuff
 c3     | stuff

T4:
 t4c1      | t4c2     | t4c3  
----------+---------+------------
 b2        | 456      | 0
 a1        | a1        | 3

T5:
 t5c1   | t5c2       | t5c3 
-------+-----------+---------------
 c3     | b2          |           2

T6:
   t6c1   |       t6c2       
---------+----------------------
 abc      | a1       
 def       | b2            
 ghi       | c3    


SQL query that works from psql:

select T4.t4c3 from T4, T5, T6 where
((T6.t6c2 = T4.t4c1 and T6.t6c1 = 'ghi') or
(T5.t5c2 = T4.t4c1 and T6.t6c2 = T5.t5c1 and T6.t6c1 = 'ghi')) and
(T4.t4c2 = '456')
union
select T5.t5c3 from T4, T5, T6
where
(T4.t4c2 = '456') and
(T5.t5c2 = T4.t4c1 and T6.t6c2 = T5.t5c1 and T6.t6c1 = 'ghi')
;
Returns:
t4c3
--------
     1
     2
(2 rows)
If I replace ghi with def
Returns:
t4c3
--------
     1
(1 row)
If I replace ghi with abc
Returns:
t4c3
--------
     3
(1 row)

Here is the function as I originally had it:

create or replace function func1(varchar, varchar) returns setof 
integer as
'select T4.t4c3 from T4, T5, T6 where
((T6.t6c2 = T4.t4c1 and T6.t6c1::varchar = $1) or 
(T5.t5c2 = T4.t4c1 and T6.t6c2 = T5.t5c1 and T6.t6c1::varchar = $1)) and
(T4.t4c2::varchar = $2)
union
select T5.t5c3 from T4, T5, T6
where
(T4.t4c2::varchar = $2) and 
(T5.t5c2 = T4.t4c1 and T6.t6c2 = T5.t5c1 and T6.t6c1::varchar = $1)
;' language sql;

select func1('abc', '456'); returns:
func1
-------
(0 rows)
select func1('def', '456'); returns:
func1
-------
(0 rows)
select func1('ghi', '456'); returns:
func1
-------
   2
(1 row)

Here is the function as I have it now that returns the same values as the sql 
query statement above:

create or replace function func1(varchar, varchar) returns setof 
integer as
'
select T5.t5c3 from T4, T5, T6
where
(T4.t4c2::varchar = $2) and
(T5.t5c2 = T4.t4c1 and T6.t6c2 = T5.t5c1 and T6.t6c1::varchar = $1)
union
select T4.t4c3 from T4, T5, T6
where
(T4.t4c2::varchar = $2) and
(T5.t5c2 = T4.t4c1 and T6.t6c2 = T5.t5c1 and T6.t6c1::varchar = $1)
union
select T4.t4c3 from T4, T5, T6
where
(T4.t4c2::varchar = $2) and (T6.t6c2 = T4.t4c1 and T6.t6c1::varchar = $1)
union
select T4.t4c3 from T4, T5, T6
where
(T4.t4c2 = ''a1'') and (T6.t6c2 = T4.t4c1 and T6.t6c1::varchar = $1)
;
' language sql;


Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2002-08-09 14:15:43
Subject: Re: Sql Functions
Previous:From: James KeltyDate: 2002-08-08 22:23:56
Subject: Proglems with 7.2.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group