Thread: Sql Functions
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;
Randy Neumann <Randy_Neumann@centralref.com> writes:
> 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'.
No, the problem is the casts that you've inserted, which are in the
wrong direction. You've cast char(n) columns to varchar, which produces
results like '456 '::varchar, which does not equal
'456'::varchar.
The reason the query works "by hand" without any casts is that the
untyped constants '456', 'ghi' are assigned type char(n) after seeing
what they are compared to. Comparison of char(n) values ignores
trailing blanks, so the query gives the answers you expect. In the
function as you've set it up, the comparisons are done under varchar
rules, and the presence or absence of trailing blanks makes a
difference.
A good general rule for novices is never to use type char(n), period.
You almost certainly want varchar(n), instead. There are very very
few applications where fixed-width character data is actually what is
wanted.
regards, tom lane
Sorry guys, I am new to Postgresql and have exhausted my reading
material. I am trying to load a "k_device" table with records from an
pipe-delimited ascii file ("/home/kurt/data/sql.device.d"). In psql, I
get the following...
<FIRST>
test1=> COPY k_device FROM '/home/kurt/data/sql.device.d' USING
DELIMITERS '|';
ERROR: You must have Postgres superuser privilege to do a COPY directly
to or from a file. Anyone can COPY to stdout or from stdin. Psql's
\copy command also works for anyone.
<\FIRST>
Okay so I am not the postgres superuser (nor can I ever be) so I try the
\copy command and I get the following...
<SECOND>
test1=> \copy "k_device" from "/home/kurt/data/sql.device.d" with
delimiters '|'
\copy: parse error at 'delimiters'
<\SECOND>
and...
<THIRD>
test1=> \copy "k_device" from "/home/kurt/data/sql.device.d" using
delimiters '|'
"/home/kurt/data/sql.device.d": No such file or directory
<\THIRD>
What am I doing wrong? Please help.
K.
Kurt Gunderson <kgunders@cbnlottery.com> writes:
> test1=> \copy "k_device" from "/home/kurt/data/sql.device.d" using
> delimiters '|'
> "/home/kurt/data/sql.device.d": No such file or directory
Almost there. Try it with single quotes or no quotes around the
filename --- double quotes are for SQL identifiers, which the filename
is not. I tried
test72=# \copy "z" to "barf" using delimiters '|'
which worked, but what I got was
-rw-r--r-- 1 tgl users 0 Aug 13 17:08 "barf"
regards, tom lane
On Tue, 2002-08-13 at 19:41, Kurt Gunderson wrote:
> Sorry guys, I am new to Postgresql and have exhausted my reading
> material. I am trying to load a "k_device" table with records from an
> pipe-delimited ascii file ("/home/kurt/data/sql.device.d"). In psql, I
> get the following...
> <SECOND>
> test1=> \copy "k_device" from "/home/kurt/data/sql.device.d" with
> delimiters '|'
>
> \copy: parse error at 'delimiters'
> <\SECOND>
>
> and...
>
> <THIRD>
> test1=> \copy "k_device" from "/home/kurt/data/sql.device.d" using
> delimiters '|'
>
> "/home/kurt/data/sql.device.d": No such file or directory
> <\THIRD>
>
> What am I doing wrong? Please help.
You're using the wrong kind of quotes. The filepath must be in single
quotes:
\copy k_device from '/home/kurt/data/sql.device.d' using delimiters '|'
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Watch ye therefore, and pray always, that ye may be
accounted worthy to escape all these things that shall
come to pass, and to stand before the Son of man."
Luke 21:36