PL/pgSQL a great procedural language for PostgreSQL
Hi all,
----I'm trying to understand this very interesting language PL/pgSQL
thanks to Jan Wieck,
----finally we can trap NULL values as in:
create function len(text) returns text as '
declare
nonullo alias for $1;
begin
if nonullo then
return length(nonullo);
else
return ''<NULL>'';
end if;
end;' language 'plpgsql';
CREATE
select len(c) from a;
len
------
12
<NULL>
<NULL>
<NULL>
12
(5 rows)
------I'm trying to create a function that returns a value instead of
NULL.
------The following one works well:
create function nvl(integer) returns integer as '
declare
nullo integer := 0;
nonullo alias for $1;
begin
if NONULLO then
return NONULLO;
else
return NULLO;
end if;
end;' language 'plpgsql';
CREATE
select nvl(i) from a;
nvl
-------
0
0
2232767
0
(4 rows)
------I would like to report this little thing, seems PL/pgSQL isn't
case insensitive:
drop function nvl(integer,integer);
DROP
create function nvl(integer) returns integer as '
declare
NULLO integer := 0;
NONULLO alias for $1;
begin
if NONULLO then
return NONULLO;
else
return NULLO;
end if;
end;' language 'plpgsql';
CREATE
select nvl(i) from a;
ERROR: attribute 'nonullo' not found
-------and seems it returns only constant values:
drop function nvl(integer,integer);
DROP
create function nvl(integer,integer) returns integer as '
declare
nonullo alias for $1;
nullo ALIAS FOR $2;
begin
if NONULLO then
return NONULLO;
else
return NULLO;
end if;
end;' language 'plpgsql';
CREATE
select nvl(i,0) from a;
nvl
-------
2232767
(4 rows)
Jose'
It appears that when a function is called if any of the paramaters are
NULL all of the parameters are NULL.
try:
drop function nvl(int, int);
create function nvl(int, int) returns boolean as '
declare
nonullo alias as $1;
nullo alias as $2;
begin
return (nonullo IS NULL) AND (nullo IS NULL);
end;' language 'plpgsql';
select nvl(i,0) from a;
you should get:
nvl
---
t
t
f
t
(4 rows)
Show quoted text
Hi all,
----I'm trying to understand this very interesting language PL/pgSQL
thanks to Jan Wieck,
----finally we can trap NULL values as in:-------and seems it returns only constant values:
drop function nvl(integer,integer);
DROP
create function nvl(integer,integer) returns integer as '
declare
nonullo alias for $1;
nullo ALIAS FOR $2;
begin
if NONULLO then
return NONULLO;
else
return NULLO;
end if;
end;' language 'plpgsql';
CREATEselect nvl(i,0) from a;
nvl
-------2232767
(4 rows)
Jose'
Import Notes
Resolved by subject fallback
It appears that when a function is called if any of the paramaters are
NULL all of the parameters are NULL.
try:
drop function nvl(int, int);
create function nvl(int, int) returns boolean as '
declare
nonullo alias as $1;
nullo alias as $2;
begin
return (nonullo IS NULL) AND (nullo IS NULL);
end;' language 'plpgsql';
select nvl(i,0) from a;
you should get:
nvl
---
t
t
f
t
(4 rows)
Don't blame PL/pgSQL for that. There is only one bool isNull
pointer given to PL handlers. How should the PL handler know,
which of the arguments are null then? As I said on another
thread, the function call interface needs to get redesigned.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
It appears that when a function is called if any of the paramaters
are
NULL all of the parameters are NULL.
try:
drop function nvl(int, int);
create function nvl(int, int) returns boolean as '
declare
nonullo alias as $1;
nullo alias as $2;
begin
return (nonullo IS NULL) AND (nullo IS NULL);
end;' language 'plpgsql';
select nvl(i,0) from a;
you should get:
nvl
---
t
t
f
t
(4 rows)Don't blame PL/pgSQL for that. There is only one bool isNull
pointer given to PL handlers. How should the PL handler know,
which of the arguments are null then? As I said on another
thread, the function call interface needs to get redesigned.
Well, Jan, don't get sensitive. I love PL/pgSQL. And I had no illusions
that it was your HANDLER causing the problem. I feel that a function
call interface redesign is also needed. But, I do have a quick
question, why does it matter which one is NULL if you can still obtain
the parameters in the order they were passed why would one become NULL
that wasn't before? I'm asking totally from ignorance here.
-DEJ
Show quoted text
Jan
Import Notes
Resolved by subject fallback
Don't blame PL/pgSQL for that. There is only one bool isNull
pointer given to PL handlers. How should the PL handler know,
which of the arguments are null then? As I said on another
thread, the function call interface needs to get redesigned.Well, Jan, don't get sensitive. I love PL/pgSQL. And I had no illusions
that it was your HANDLER causing the problem. I feel that a function
call interface redesign is also needed. But, I do have a quick
question, why does it matter which one is NULL if you can still obtain
the parameters in the order they were passed why would one become NULL
that wasn't before? I'm asking totally from ignorance here.
It might be possible, that even if *isNull is true to look at
the actual arguments given to the PL handler. For datatypes
passed by reference, a NULL value has to get passed as null
pointer. I'm not 100% sure if that is really true in all
cases where PL functions can get called, and we all know what
happens when accessing a pointer that points to something
else than a memory location. For arguments passed by value it
is totally impossible to know if it's a NULL by looking at
the value itself.
Summary is, that the PL handler cannot be sure which of the
arguments the function caller meant when calling with *isNull
= TRUE. And I decided for now to be safe and assume he meant
all.
When accessing data from a specific table, it is possible to
call the function with a complex type. This time, the
PL/pgSQL function gets the complete tuple and can look at the
information there which attributes are NULLs.
CREATE TABLE a (k integer, i integer);
CREATE FUNCTION a_i_checknull(a) RETURNS bool AS '
DECLARE
row_a ALIAS FOR $1; -- The dot-notation $1.i does not work!
BEGIN
IF row_a.k ISNULL THEN
RAISE NOTICE ''attribute k has NULL value'';
END IF;
IF row_a.i ISNULL THEN
RAISE NOTICE ''attribute i has NULL value'';
END IF;
IF row_a.k ISNULL OR row_a.i ISNULL THEN
RETURN ''t'';
END IF;
RETURN ''f'';
END;
' LANGUAGE 'plpgsql';
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
For arguments passed by value it
is totally impossible to know if it's a NULL by looking at
the value itself.
Is this a sufficient reason to move toward having all user-oriented data
types be pass-by-reference? If we don't do that, then we would need to
pass a null flag for every parameter, or an array of flags, or have some
global array which contains the null flags (which we might get away with
since Postgres is pretty much single threaded and non-reentrant in the
backend code). What other options might there be?
- Tom
Thomas G. Lockhart wrote:
For arguments passed by value it
is totally impossible to know if it's a NULL by looking at
the value itself.Is this a sufficient reason to move toward having all user-oriented data
types be pass-by-reference? If we don't do that, then we would need to
pass a null flag for every parameter, or an array of flags, or have some
global array which contains the null flags (which we might get away with
since Postgres is pretty much single threaded and non-reentrant in the
backend code). What other options might there be?
Perhaps make the isNull flag a bitmap ?
In case it is an int (I haven't looked), we get room for flagging 32
arguments
Also I hope that most current code will run unchanged, in case it tests
for
0 / not 0
----------------
Hannu
have anyone tested 6.4 on a libc6 based linux (for example debian hamm) ?
because I tested it on an older system (libc5 based) and it's working
well.
but on libc6 it's compiled without errors,
I can make the initdb, and start the postmaster,
but psql -l for example not working
every postgres backend exits after postmaster forked..
any idea?
redax
.----------------------------------------------------------.
|Zsolt Varga | tel/fax: +36 36 422811 |
| AgriaComputer LTD | email: redax@agria.hu |
| System Administrator | URL: http://www.agria.hu/ |
`----------------------------------------------------------'
Zsolt Varga wrote:
have anyone tested 6.4 on a libc6 based linux (for example debian hamm) ?
because I tested it on an older system (libc5 based) and it's working
well.but on libc6 it's compiled without errors,
I can make the initdb, and start the postmaster,
but psql -l for example not working
every postgres backend exits after postmaster forked..any idea?
Does createdb work ?
-----------
Hannu
Perhaps make the isNull flag a bitmap ?
In case it is an int (I haven't looked), we get room for flagging 32
arguments
Hmm. Well, how about if we look at what we would prefer if we were
allowed to start from the beginning, and then consider this as an
alternative. Doing this with a bitmap might be a bit arcane.
Also I hope that most current code will run unchanged, in case it
tests for 0 / not 0
Good point.
- Thomas
[Since my work email didn't go through on the list here's a short answer
again. ]
On Fri, Nov 06, 1998 at 10:33:48AM +0100, Zsolt Varga wrote:
have anyone tested 6.4 on a libc6 based linux (for example debian hamm) ?
Yes, me. That is my debian system is newer than hamm.
but on libc6 it's compiled without errors,
I can make the initdb, and start the postmaster,
but psql -l for example not working
every postgres backend exits after postmaster forked..
Works fine for me. No problem at all.
Michael
--
Dr. Michael Meskes, Leiter Niederlassung West, Datenrevision GmbH
business: Cuxhavener Str. 36, D-21149 Hamburg
private: Th.-Heuss-Str. 61, D-41812 Erkelenz, Michael.Meskes@usa.net
Go SF49ers! Go Rhein Fire! Use Debian GNU/Linux!
Hi all
On Fri, 6 Nov 1998, Michael Meskes wrote:
On Fri, Nov 06, 1998 at 10:33:48AM +0100, Zsolt Varga wrote:
have anyone tested 6.4 on a libc6 based linux (for example debian hamm) ?
Yes, me. That is my debian system is newer than hamm.
but on libc6 it's compiled without errors,
I can make the initdb, and start the postmaster,
but psql -l for example not working
every postgres backend exits after postmaster forked..Works fine for me. No problem at all.
This may or may not be related to your problem, but...
On my server I did only the min. install of Red Hat 4.2 that I needed to
run a server, then custom built most of the stuff such as PHP, Apache,
PostgreSQL ... etc. I also change the permissions on many directories and
files.
And now I find that a normal user can not do some things that involve
'fork()'. But all works fine as root, so it is a permission problem,
which I still have not pinned down, not even shure how to find it.:(
Example: Pine/ispell or Pine/vim
Any attemp to run an external program from pine, such as ispell, will not
work, pine will say that the program returned 255. So I did some debuging
stuff and found the problem happens at the fork() call.
Does this sound like your problem?
If so, then start looking very closely at permissions.
Have a great day, and hope that helped.
Terry Mackintosh <terry@terrym.com> http://www.terrym.com
sysadmin/owner Please! No MIME encoded or HTML mail, unless needed.
Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3
-------------------------------------------------------------------
Success Is A Choice ... book by Rick Patino, get it, read it!
On Sat, Nov 07, 1998 at 08:37:51AM -0500, Terry Mackintosh wrote:
but on libc6 it's compiled without errors,
I can make the initdb, and start the postmaster,
but psql -l for example not working
every postgres backend exits after postmaster forked..Works fine for me. No problem at all.
This may or may not be related to your problem, but...
Ehem, I do not have that problem. I was the one answering that my
installation is okay. :-9
And now I find that a normal user can not do some things that involve
'fork()'. But all works fine as root, so it is a permission problem,
which I still have not pinned down, not even shure how to find it.:(
I wonder which permissions have to do with fork().
Michael
--
Dr. Michael Meskes, Leiter Niederlassung West, Datenrevision GmbH
business: Cuxhavener Str. 36, D-21149 Hamburg
private: Th.-Heuss-Str. 61, D-41812 Erkelenz, Michael.Meskes@usa.net
Go SF49ers! Go Rhein Fire! Use Debian GNU/Linux!
On Fri, 6 Nov 1998, Hannu Krosing wrote:
|Does createdb work ?
yeah worked,
somebody sent me the solution from the list,
I have to remove termcap-compat from debian/hamm
and recompile... after it works well
great work, it's 3-4 times faster than 6.3.2 was
thanks
redax
.----------------------------------------------------------.
|Zsolt Varga | tel/fax: +36 36 422811 |
| AgriaComputer LTD | email: redax@agria.hu |
| System Administrator | URL: http://www.agria.hu/ |
`----------------------------------------------------------'