Hi all,
I'm currently coding some functions in plpgsql for generating
reports out of records in a table. Problem is: NULL values in
records make the complete function fail.
Here is a simple test case (original is more complex with a multi-
table query in the for qres in select... part):
create table test(
id serial,
descr char(4),
data int
);
insert into test (descr, data) values ('set1', 15);
-- record 2 does not have a data value --
insert into test (descr) values ('set2');
create function report(int) returns text as '
declare
qres record;
report text;
begin
for qres in
select descr, data from test where id=$1
loop
report:=qres.de scr||'': ''||qres.data;
end loop;
return report;
end;'
language 'plpgsql';
now test the function in psql:
test=> select report(1);
report
----------
set1: 15
(1 row)
test=> select report(2);
report
--------
(1 row)
while what I want it to return in the second case is this:
report
--------
set2:
(1 row)
'set2: NULL' would be ok as well.
How can I achieve this?
Regards,
Daniel
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match 1 2263
concatenating a NULL value to a string will return NULL.
SELECT NULL||'Stringgg g';
?column?
----------
(1 row)
There's a fonction called coalesce that replaces NULL values with a
specified value it can be usefull:
SELECT coalesce(NULL,' ') || ' Stringgggg';
?column?
-------------
Stringgggg
(1 row)
SELECT coalesce('A','' ) || ' Stringgggg';
?column?
--------------
A Stringgggg
(1 row)
HTH
Najib.
----- Original Message -----
From: "Daniel Martini" <dm******@uni-hohenheim.de>
To: <pg***********@ postgresql.org>
Sent: Friday, September 24, 2004 11:33 AM
Subject: [GENERAL] plpgsql-fct. fails on NULL in record variables Hi all,
I'm currently coding some functions in plpgsql for generating reports out of records in a table. Problem is: NULL values in records make the complete function fail. Here is a simple test case (original is more complex with a multi- table query in the for qres in select... part):
create table test( id serial, descr char(4), data int );
insert into test (descr, data) values ('set1', 15); -- record 2 does not have a data value -- insert into test (descr) values ('set2');
create function report(int) returns text as ' declare qres record; report text; begin for qres in select descr, data from test where id=$1 loop report:=qres.de scr||'': ''||qres.data; end loop; return report; end;' language 'plpgsql';
now test the function in psql: test=> select report(1); report ---------- set1: 15 (1 row)
test=> select report(2); report --------
(1 row)
while what I want it to return in the second case is this: report -------- set2: (1 row)
'set2: NULL' would be ok as well.
How can I achieve this?
Regards, Daniel
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Mark Mikulec |
last post by:
Hi there,
I wonder if anyone can shed some light on a very frustrating problem.
I'm running a debian linux 3.0 "woody" server, nothing special, with
the latest version of postres that apt-get will allow me, which I
*think* it;s 7.1 something, I don't know how to figure out the
postgres version. Anywho - I'm trying to backup my databases, which I
did at one point, but I have no idea what happened, could have been an
upgrade. My Dbs...
|
by: Amin Schoeib |
last post by:
Hi,
When I Try to write Functions I always become the error
That the language plpgsql doesn't exist.
I tried it this way:
createlang plpgsql template1
createlang -l template1
Procedural languages
Name | Trusted?
---------+----------
|
by: Thomas LeBlanc |
last post by:
I copied an example from the help:
CREATE FUNCTION somefunc() RETURNS integer AS '
DECLARE
quantity integer := 30;
BEGIN
RAISE NOTICE ''Quantity here is %'', quantity; -- Quantity here is 30
quantity := 50;
--
-- Create a subblock
|
by: Martin Marques |
last post by:
We are trying to make some things work with plpgsql. The problem is that I
built several functions that call one another, and I thought that the way of
calling it was just making the assign:
var:=func1(arg1,arg2);
which gave me an error near ")".
Now if I did the same, but like this:
|
by: Rajesh Kumar Mallah |
last post by:
Hi,
profile_row profile_master%ROWTYPE;
in a plpgsql function
gives the error below
tradein_clients=# SELECT general.create_accounts();
WARNING: plpgsql: ERROR during compile of create_accounts near line 8
| |
by: Steve Wampler |
last post by:
Hmmm, I've always used plpgsql.so (also formerly known
as libplpgsql.so, I think...) as in:
CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler
AS '/usr/lib/plpgsql.so', 'plpgsql_call_handler'
LANGUAGE c;
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER
plpgsql_call_handler;
|
by: lnd |
last post by:
After copied pg database from one PC to another
-I could not find plpgsql function(s) in the copied database.
-had to instal plpgsql language handler again
-whilst tables and data moved fine
The copy included all under /cygwin/usr/local/pgsql/data and database was
down while making a copy.
|
by: Karl O. Pinc |
last post by:
Hi,
Thought perhaps some other eyes than mine can tell if I'm doing
something wrong here or if there's a bug somewhere. I've never
passed a ROWTYPE varaible to a function but I don't see where
the problem is.
I keep getting errors like (the first is my debug output):
NOTICE: last cycle is: 11
WARNING: Error occurred while executing PL/pgSQL function
|
by: Mark Cave-Ayland |
last post by:
Hi everyone,
I'm trying to write a recursive plpgsql function in PostgreSQL 7.4.2
that given a tree node id (ictid) will return all the nodes below it in
the tree, one row per node. When I try and execute the function I get
the following error message:
CONTEXT: PL/pgSQL function "findsubcategories" line 15 at for over
select rows
PL/pgSQL function "findsubcategories" line 15 at for over select rows
|
by: sripathy sena |
last post by:
Hi,
I am trying to install OPenacs with postgres 7.4.3 as
the database.
The openacs requires plpgsql to be installed. When I
try to do this by running "CREATELANG plpgsql
template1". I get a message file plpgsql not found in
lib directory.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |