[SQL] PL/pgSQL question
Hi all, I have a question regarding functions. How can I return zero rows from a function whose return type is a table row? I did the following test and it did not work as expected: CREATE OR REPLACE FUNCTION fn_get_user (integer) RETURNS usertable AS ' DECLARE in_userid ALIAS for $1; resulter usertable%ROWTYPE; BEGIN IF in_userid IS NULL THEN RAISE EXCEPTION ''No user provided''; RETURN null; END IF; SELECT INTO resulter usertable.* FROM usertable WHERE id = in_userid; IF FOUND THEN RETURN resulter; ELSE RETURN null; END IF; END;'LANGUAGE plpgsql; >select * from fn_get_user(-1); id | col1 | col2 | col3| name | email ++-++ || ||| | (1 row) This returns a null row. I am trying to make it behave such that it returns zero rows like a straight select. >select * from usertable where id =-1; id | col1 | col2 | col3| name | email ++-++ (0 rows) Is this possible in anyway? Regards, Sebastian
Re: [SQL] PL/pgSQL question
Sebastian Ritter написа: Hi all, I have a question regarding functions. How can I return zero rows from a function whose return type is a table row? I did the following test and it did not work as expected: [...] CREATE OR REPLACE FUNCTION foobar(boolean) RETURNS SETOF x AS $_$ DECLARE res x%ROWTYPE; BEGIN if $1 THEN RETURN NEXT res; ELSE RETURN; END IF; END;$_$ LANGUAGE plpgsql; -- Milen A. Radev ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] FUNCTIONs and CASTs
Last night I tore my hair out for about three hours with the following problem (v8.3.0): I had a simple scalar query that I wanted to create a function for. However, when I created the function and called it from another query OR the PSQL command line, it was two orders of magnitude SLOWER than when I manually substituted the parameters into the query and ran it directly. Then I figured out what the cause was: The manual query was doing an indexed column lookup on the value, a simple text string. However, when the function was passed the text string as the value, it was comparing the TEXT type of the parameter to the CHAR( n) type of the indexed column, and apparently rewriting the comparison using a cast of the indexed column. Needless to say, the does not result in an indexed access (probably the index is searched sequentially for a match). I solved the problem by explicitly casting the function parameter to the type of the index, and that solved the problem. So, is this the best (or only) way to solve this? I haven't done exhaustive checking, but it appears that specifying the type of parameters in the function prototype is only used for type-checking (and function matching), and no conversion between types is done. Given that, I'm not sure of the value of the /tablename/./columnname/%TYPE notation, especially since apparently it can only be used in the function prototype and not in the body of the function. If I am wrong on any of the above, I would be pleased to know it. -- Dean -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce.
Re: [SQL] FUNCTIONs and CASTs
"Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes:
> So, is this the best (or only) way to solve this? I haven't done
> exhaustive checking, but it appears that specifying the type of
> parameters in the function prototype is only used for type-checking (and
> function matching), and no conversion between types is done.
It's not exactly clear what you checked, but it works as expected for
me. See test case below, proving that indexscan works just fine with
a parameter declared using %type.
regards, tom lane
regression=# create table tt(f1 char(10) unique);
NOTICE: CREATE TABLE / UNIQUE will create implicit index "tt_f1_key" for table
"tt"
CREATE TABLE
regression=# insert into tt select x from generate_series(1,10) x;
INSERT 0 10
regression=# \timing
Timing is on.
regression=# select * from tt where f1 = '12345';
f1
12345
(1 row)
Time: 47.589 ms
regression=# set enable_indexscan TO 0;
SET
Time: 3.146 ms
regression=# set enable_bitmapscan TO 0;
SET
Time: 1.583 ms
regression=# select * from tt where f1 = '12345';
f1
12345
(1 row)
Time: 414.585 ms
regression=# select * from tt where f1 = '12345';
f1
12345
(1 row)
Time: 412.167 ms
regression=# reset enable_indexscan;
RESET
Time: 3.037 ms
regression=# select * from tt where f1 = '12345';
f1
12345
(1 row)
Time: 4.019 ms
regression=# create function foo (tt.f1%type) returns char(10) as $$
declare r tt.f1%type;
begin
select f1 into r from tt where f1 = $1;
return r;
end$$ language plpgsql;
NOTICE: type reference tt.f1%TYPE converted to character
CREATE FUNCTION
Time: 8.193 ms
regression=# \df foo
List of functions
Schema | Name | Result data type | Argument data types
+--+--+-
public | foo | character| character
(1 row)
regression=# select foo('12345'::text);
foo
12345
(1 row)
Time: 21.683 ms
regression=# select foo('12345'::text);
foo
12345
(1 row)
Time: 4.098 ms
regression=#
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] FUNCTIONs and CASTs
On 2008-02-14 15:19, Tom Lane wrote: It's not exactly clear what you checked, but it works as expected for me. See test case below, proving that indexscan works just fine with a parameter declared using %type. regards, tom lane Consider: CREATE TABLE zzz( aaa CHAR( 10 ) ); CREATE FUNCTION dummy( zzz.aaa%TYPE ) RETURNS zzz.aaa%TYPE LANGUAGE SQL AS 'SELECT * FROM zzz WHERE CAST( $1 AS zzz.aaa%TYPE) = aaa'; The notation "zzz.aaa%TYPE" fails when specified in the cast; it's fine in the function prototype. However, specifying it in the function prototype doesn't appear to help the performance issue: Here is the actual function that caused be heartburn. The types in the function prototype match EXACTLY the types of the actual parameters being passed (and I also tried it with the tablename.columnname%TYPE notation), and yet this function is slow. However, if I replace the "$1" in the function body with "CAST( $1 AS CHAR( 10 ) )", the function is very fast. Note that ALL of the column names in the function below are indexed, so this function should be very fast (and is, with the CASTs). CREATE OR REPLACE FUNCTION "Functions".prior_call( CHAR( 10 ), CHAR( 9 ), DATE) RETURNS BOOLEAN STABLE RETURNS NULL ON NULL INPUT LANGUAGE SQL AS $SQL$ SELECT COALESCE( (SELECT TRUE FROMlic_hd NATURAL JOINlic_en NATURAL JOINlic_am WHERE$1 = licensee_id AND $2 IN( callsign, prev_callsign ) AND $3 > grant_date LIMIT 1), (SELECT TRUE FROM_preuls WHERE$1 = licensee_id AND $2 IN( callsign, prev_callsign ) LIMIT 1), FALSE ) $SQL$; So, I think you can see why it would be nice if the tablename.columnname%TYPE notation could be used in the function body. I'm not asking for that as an enhancement; rather, I'm trying to understand what the tablename.columnname%TYPE notation accomplishes, since specifying it in the function prototype doesn't appear to accomplish anything (at least for me) over just specifying "TEXT". ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] inhibit rounding on numeric columns
Hi!
Is there an easy way to enforce strict handling of numeric values with
scales, i.e. raise an exception/error instead of rounding values to the
specified scale?
In a given schema with 250+ tables I have lots of numeric columns with a
scale > 0. The docs (chapter 8.1.2) state clearly that greater scales are
rounded: "If the scale of a value to be stored is greater than the declared
scale of the column, the system will round the value to the specified number
of fractional digits."
This works as designed:
create table dummy (pi numeric(5,4));
insert into dummy values(3.141); -- insert #1
insert into dummy values(3.1415); -- insert #2
insert into dummy values(3.14159); -- insert #3
insert into dummy values('3.14159'); -- insert #4
postgres=# select * from dummy;
pi
3.1410
3.1415
3.1416
3.1416
(4 rows)
I wonder if there is a generic/transparent way (say a config parameter) to
force postgresql to raise an error for inserts #3 and #4. If there is no
easy way, what other alternatives exist? Sadly, changing the schema is
hardly a possibility for me :-(
Any suggestions are appreciated!
Note: Using PostgreSQL 8.3 with JDBC.
-- Horst Dehmer
Re: [SQL] inhibit rounding on numeric columns
On Fri, Feb 15, 2008 at 1:28 AM, Horst Dehmer <[EMAIL PROTECTED]> wrote: > > > Hi! > > Is there an easy way to enforce strict handling of numeric values with > scales, i.e. raise an exception/error instead of rounding values to the > specified scale? I can't think of an easy way. But you can always make your own type I guess. > Sadly, changing the schema is hardly a possibility for me :-( Sadly, the people who have burdened you with this problem don't seem to have fully understood the implications of rounding and the SQL specification's take on when it happens. Can you not even just make it wider? I mean, pgsql can change types on the fly, and since the value's will fit in newer, wider numerics, then you have an easy solution. Anything else you do is going to far more invasive than changing the schema. Or you'll have to handle all this in code somewhere. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] inhibit rounding on numeric columns
On 15 feb 2008, at 08.28, Horst Dehmer wrote:
Is there an easy way to enforce strict handling of numeric values
with scales, i.e. raise an exception/error instead of rounding
values to the specified scale?
insert into dummy values(3.141); -- insert #1
insert into dummy values(3.1415); -- insert #2
insert into dummy values(3.14159); -- insert #3
insert into dummy values('3.14159'); -- insert #4
I wonder if there is a generic/transparent way (say a config
parameter) to force postgresql to raise an error for inserts #3 and
#4.
I don't know a way to achieve what you ask for, and I'm not sure I
think there should be. Have you thought about the consequences of
treating 3.1415 as being different from 3.1415000? If you do a
SELECT 3.1415 = 3.1415000 it will always evaluate to true, which it
should, no matter to which precision you cast the operands. Numeric
scale is not the equivalent of character string length.
What is the actual problem you're trying to solve?
Sincerely,
Niklas Johansson
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] inhibit rounding on numeric columns
You could probably create a before insert trigger which compares the number of
fractional digits in the given number with the defined scale (surely some
system table can offer you that) and raises an exception if needed. I do agree
though with Niklas Johansson's remarks and wonder with him on what exactly
you're trying to accomplish.
>>> "Horst Dehmer" <[EMAIL PROTECTED]> 2008-02-15 8:28 >>>
Hi!
Is there an easy way to enforce strict handling of numeric values with scales,
i.e. raise an exception/error instead of rounding values to the specified scale?
In a given schema with 250+ tables I have lots of numeric columns with a scale
> 0. The docs (chapter 8.1.2) state clearly that greater scales are rounded:
"If the scale of a value to be stored is greater than the declared scale of the
column, the system will round the value to the specified number of fractional
digits."
This works as designed:
create table dummy (pi numeric(5,4));
insert into dummy values(3.141); -- insert #1
insert into dummy values(3.1415); -- insert #2
insert into dummy values(3.14159); -- insert #3
insert into dummy values('3.14159'); -- insert #4
postgres=# select * from dummy;
pi
3.1410
3.1415
3.1416
3.1416
(4 rows)
I wonder if there is a generic/transparent way (say a config parameter) to
force postgresql to raise an error for inserts #3 and #4. If there is no easy
way, what other alternatives exist? Sadly, changing the schema is hardly a
possibility for me :-(
Any suggestions are appreciated!
Note: Using PostgreSQL 8.3 with JDBC.
-- Horst Dehmer
