pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNO DATA FOUND Exception
7+ messages / 4 participants
[nested] [flat]
* NO DATA FOUND Exception
@ 2007-06-25 19:20 Fernando Hevia <fhevia@ip-tel.com.ar>
0 siblings, 2 replies; 7+ messages in thread
From: Fernando Hevia @ 2007-06-25 19:20 UTC (permalink / raw)
To: pgsql-sql
Hi.
Im taking my first steps with plpgsql.
I want my function to react to the result of a query in the following way:
begin
select column into variable from table where condition;
exception
when <<data_found>> then return variable;
when <<no_data_found>> then <<do nothing/continue>> ;
when <<any_other_exception>> then <<raise some error message>> ;
end ;
Is something like this possible en plpgsql without recurring to a select
count(*) to check how many results I will get?
Actual code is:
CREATE OR REPLACE FUNCTION test(p_line text) RETURNS text AS
$body$
DECLARE
v_len integer DEFAULT 8;
v_search varchar;
v_register num_geo%ROWTYPE;
BEGIN
-- Search loop
WHILE v_len > 0 LOOP
v_search := substring(p_line, 1, v_len);
begin
SELECT * INTO v_register WHERE prefix = v_search;
exception
when no_data then -- Getting error here
continue;
when others then
return v_register.prefix;
end;
v_len := v_len - 1;
END LOOP;
raise 'Not found';
END;
$body$
LANGUAGE 'plpgsql' VOLATILE ;
ERROR: unrecognized exception condition "no_data"
SQL state: 42704
Context: compile of PL/pgSQL function "test" near line 14
Thanks,
Fernando.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: NO DATA FOUND Exception
@ 2007-06-25 19:35 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Fernando Hevia <fhevia@ip-tel.com.ar>
1 sibling, 1 reply; 7+ messages in thread
From: Andrew Sullivan @ 2007-06-25 19:35 UTC (permalink / raw)
To: pgsql-sql
On Mon, Jun 25, 2007 at 04:20:37PM -0300, Fernando Hevia wrote:
> when <<data_found>> then return variable;
> when <<no_data_found>> then <<do nothing/continue>> ;
> when <<any_other_exception>> then <<raise some error message>> ;
Check out the FOUND variable in the documentation for the first two,
and the "trapping errors" section for the latter.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: NO DATA FOUND Exception
@ 2007-06-25 19:55 Fernando Hevia <fhevia@ip-tel.com.ar>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 7+ messages in thread
From: Fernando Hevia @ 2007-06-25 19:55 UTC (permalink / raw)
To: 'Andrew Sullivan' <ajs@crankycanuck.ca>; pgsql-sql
>On Mon, Jun 25, 2007 at 04:20:37PM -0300, Fernando Hevia wrote:
>> when <<data_found>> then return variable;
>> when <<no_data_found>> then <<do nothing/continue>> ;
>> when <<any_other_exception>> then <<raise some error message>> ;
>
>Check out the FOUND variable in the documentation for the first two,
>and the "trapping errors" section for the latter.
>
>Andrew Sullivan | ajs@crankycanuck.ca
Thanks for the tip. I was looking in the wrong place.
The FOUND variable is explained in chapter "37.6.6. Obtaining the Result
Status".
Thanks again,
Fernando.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: NO DATA FOUND Exception
@ 2007-06-25 20:05 Michael Glaesemann <grzm@seespotcode.net>
parent: Fernando Hevia <fhevia@ip-tel.com.ar>
1 sibling, 2 replies; 7+ messages in thread
From: Michael Glaesemann @ 2007-06-25 20:05 UTC (permalink / raw)
To: Fernando Hevia <fhevia@ip-tel.com.ar>; +Cc: pgsql-sql
[Please create a new message to post about a new topic, rather than
replying to and changing the subject of a previous message. This will
allow mail clients which understand the References: header to
properly thread replies.]
On Jun 25, 2007, at 14:20 , Fernando Hevia wrote:
> Is something like this possible en plpgsql without recurring to a
> select
> count(*) to check how many results I will get?
I think you want to look at FOUND.
http://www.postgresql.org/docs/8.2/interactive/plpgsql-
statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
For example:
# select * from foos;
foo
-----
bar
baz
bat
(3 rows)
# CREATE FUNCTION foos_exist()
RETURNS boolean
LANGUAGE plpgsql AS $body$
DECLARE
v_foo TEXT;
BEGIN
SELECT INTO v_foo
foo
FROM foos;
IF FOUND THEN RETURN TRUE;
ELSE RETURN FALSE;
END IF;
END;
$body$;
CREATE FUNCTION
# select foos_exist();
foos_exist
------------
t
(1 row)
# truncate foos;
TRUNCATE TABLE
test=# select foos_exist();
foos_exist
------------
f
(1 row)
> Actual code is:
>
> CREATE OR REPLACE FUNCTION test(p_line text) RETURNS text AS
> $body$
> DECLARE
> v_len integer DEFAULT 8;
> v_search varchar;
> v_register num_geo%ROWTYPE;
> BEGIN
>
> -- Search loop
> WHILE v_len > 0 LOOP
> v_search := substring(p_line, 1, v_len);
> begin
> SELECT * INTO v_register WHERE prefix = v_search;
> exception
> when no_data then -- Getting error here
> continue;
> when others then
> return v_register.prefix;
> end;
> v_len := v_len - 1;
> END LOOP;
I think you might want to rewrite this using some of the information
here:
http://www.postgresql.org/docs/8.2/interactive/plpgsql-control-
structures.html#PLPGSQL-RECORDS-ITERATING
For example, your inner loop could loop could look something like this:
FOR v_register IN
SELECT *
FROM <table>
WHERE prefix = v_search
LOOP
return v_register.prefix;
END LOOP;
If no data is found, the loop won't do anything.
However, it looks like you're trying to return a set of results
(i.e., many rows), rather than just a single row. You'll want to look
at set returning functions. One approach (probably not the best)
would be to expand p_line into all of the possible v_search items and
append that to your query, which would look something like:
SELECT prefix
FROM
<table>
WHERE prefix IN (<list of v_search items>).
Another way to do this might be to not use a function at all, but a
query along the lines of
SELECT prefix
FROM <table>
WHERE p_line LIKE prefix || '%';
Hope this helps.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: NO DATA FOUND Exception
@ 2007-06-26 14:25 Fernando Hevia <fhevia@ip-tel.com.ar>
parent: Michael Glaesemann <grzm@seespotcode.net>
1 sibling, 0 replies; 7+ messages in thread
From: Fernando Hevia @ 2007-06-26 14:25 UTC (permalink / raw)
To: 'Michael Glaesemann' <grzm@seespotcode.net>; +Cc: pgsql-sql
On Jun 25, 2007, at 17:05, Michael Glaesemann wrote:
>[Please create a new message to post about a new topic, rather than
>replying to and changing the subject of a previous message. This will
>allow mail clients which understand the References: header to
>properly thread replies.]
Wasn't aware of this. Will do.
I should obtain a better mail client.
>However, it looks like you're trying to return a set of results
>(i.e., many rows), rather than just a single row. You'll want to look
>at set returning functions. One approach (probably not the best)
>would be to expand p_line into all of the possible v_search items and
>append that to your query, which would look something like:
Thank you for your help. All the advice was very useful and I have now a
working function.
I still have an issue left: I would like my function to return multiple
values (as in columns of a row).
Actually I found two possibilities: array and record. I ended up using
arrays since I couldn't figure out how to access the record data from
outside the function. Nevertheless I think a solution based on returning a
record type when you actually want to return the whole row would be more
elegant.
For example:
CREATE TABLE table1 (
field1 text,
field2 text,
field3 text
);
INSERT INTO table1 ('data1', 'data2', 'data3');
CREATE FUNCTION my_func() RETURNS record AS
$body$
DECLARE
v_row table1%ROWTYPE;
BEGIN
SELECT *
INTO v_row
FROM table1
WHERE <condition> ;
IF FOUND THEN
RETURN v_row;
END IF;
RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql';
SELECT my_func();
my_func
---------------------------------------------------
(data1, data2, data3)
How do I refer a specific field of the returned row from outside the
function? How should I write the query in order to show only fields 1 and 3,
for example?
It's sad to bother with this syntax questions, but I've had a hard time
finding code examples online.
Regards,
Fernando.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: NO DATA FOUND Exception
@ 2007-06-26 14:36 Bart Degryse <Bart.Degryse@indicator.be>
parent: Michael Glaesemann <grzm@seespotcode.net>
1 sibling, 1 reply; 7+ messages in thread
From: Bart Degryse @ 2007-06-26 14:36 UTC (permalink / raw)
To: pgsql-sql
In case you would like to use set returning functions...
if your function will return records with the same structure as an existing table
CREATE FUNCTION my_func() RETURNS SETOF my_table AS ...
if not you have to define the returning type
CREATE TYPE func_row AS ("field1" varchar(10), "field2" integer, "field3" integer, ...)
CREATE FUNCTION my_func() RETURNS SETOF func_row AS ...
now you can use your function
SELECT * FROM my_func();
or
SELECT A.field1, A.field2
FROM my_func() A left join my_func() B on A.field2 = B.field3
WHERE A.field1 like 'B%';
>>> "Fernando Hevia" <fhevia@ip-tel.com.ar> 2007-06-26 16:25 >>>
On Jun 25, 2007, at 17:05, Michael Glaesemann wrote:
>[Please create a new message to post about a new topic, rather than
>replying to and changing the subject of a previous message. This will
>allow mail clients which understand the References: header to
>properly thread replies.]
Wasn't aware of this. Will do.
I should obtain a better mail client.
>However, it looks like you're trying to return a set of results
>(i.e., many rows), rather than just a single row. You'll want to look
>at set returning functions. One approach (probably not the best)
>would be to expand p_line into all of the possible v_search items and
>append that to your query, which would look something like:
Thank you for your help. All the advice was very useful and I have now a
working function.
I still have an issue left: I would like my function to return multiple
values (as in columns of a row).
Actually I found two possibilities: array and record. I ended up using
arrays since I couldn't figure out how to access the record data from
outside the function. Nevertheless I think a solution based on returning a
record type when you actually want to return the whole row would be more
elegant.
For example:
CREATE TABLE table1 (
field1 text,
field2 text,
field3 text
);
INSERT INTO table1 ('data1', 'data2', 'data3');
CREATE FUNCTION my_func() RETURNS record AS
$body$
DECLARE
v_row table1%ROWTYPE;
BEGIN
SELECT *
INTO v_row
FROM table1
WHERE <condition> ;
IF FOUND THEN
RETURN v_row;
END IF;
RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql';
SELECT my_func();
my_func
---------------------------------------------------
(data1, data2, data3)
How do I refer a specific field of the returned row from outside the
function? How should I write the query in order to show only fields 1 and 3,
for example?
It's sad to bother with this syntax questions, but I've had a hard time
finding code examples online.
Regards,
Fernando.
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: NO DATA FOUND Exception
@ 2007-06-26 16:15 Fernando Hevia <fhevia@ip-tel.com.ar>
parent: Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 0 replies; 7+ messages in thread
From: Fernando Hevia @ 2007-06-26 16:15 UTC (permalink / raw)
To: 'Bart Degryse' <Bart.Degryse@indicator.be>; pgsql-sql
>>"Fernando Hevia" <fhevia@ip-tel.com.ar> 2007-06-26 16:25 >>>
>>How do I refer a specific field of the returned row from outside the
>>function? How should I write the query in order to show only fields 1 and
3, for example?
>In case you would like to use set returning functions...
>
>if your function will return records with the same structure as an existing
>table
>CREATE FUNCTION my_func() RETURNS SETOF my_table AS ...
>
>if not you have to define the returning type
>CREATE TYPE func_row AS ("field1" varchar(10), "field2" integer, "field3"
>integer, ...)
>CREATE FUNCTION my_func() RETURNS SETOF func_row AS ...
>
>now you can use your function
>SELECT * FROM my_func();
>
>or
>
>SELECT A.field1, A.field2
>FROM my_func() A left join my_func() B on A.field2 = B.field3
>WHERE A.field1 like 'B%';
Exactly what I was looking for.
Thanks!!
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-06-26 16:15 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-25 19:20 NO DATA FOUND Exception Fernando Hevia <fhevia@ip-tel.com.ar>
2007-06-25 19:35 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-06-25 19:55 ` Fernando Hevia <fhevia@ip-tel.com.ar>
2007-06-25 20:05 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-26 14:25 ` Fernando Hevia <fhevia@ip-tel.com.ar>
2007-06-26 14:36 ` Bart Degryse <Bart.Degryse@indicator.be>
2007-06-26 16:15 ` Fernando Hevia <fhevia@ip-tel.com.ar>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox