[SQL] Text cast problem
Dear all- I encountered the following problem: select * from parcel where number = '255 ' gives me 1 row but select * from parcel where number = '255 ' ::text does give me 0 rows. The cast removes the trailing empty character. Why is that? Unfortunately the statements are generated (NPGSQL) and I can’t change the way they are generated. What can I do? Any suggestions highly welcomed! Volkmar
Re: [SQL] Text cast problem
On Thursday 02 April 2009 2:51:30 am Volkmar Herbst wrote: > Dear all- > > I encountered the following problem: > > > > select * from parcel where number = '255 ' > > gives me 1 row but > > > > select * from parcel where number = '255 ' ::text > > does give me 0 rows. The cast removes the trailing empty character. > > > > Why is that? Unfortunately the statements are generated (NPGSQL) and I > can’t change the way they are generated. What can I do? Any suggestions > highly welcomed! The why is here: http://www.postgresql.org/docs/8.3/interactive/datatype-character.html In particular: "Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way. However, the padding spaces are treated as semantically insignificant. Trailing spaces are disregarded when comparing two values of type character, and they will be removed when converting a character value to one of the other string types. Note that trailing spaces are semantically significant in character varying and text values. " What I have done in a similar situation is put a trigger on the table that trims the strings on INSERT or UPDATE. > > > > Volkmar -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FUNCTION problem
On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: > > > Now I remember. Its something that trips me up, the RECORD in RETURN setof > RECORD is not the same thing as the RECORD in DECLARE RECORD. See below for > a better explanation- > http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html#PL >PGSQL-DECLARATION-RECORDS Note that RECORD is not a true data type, only a > placeholder. One should also realize that when a PL/pgSQL function is > declared to return type record, this is not quite the same concept as a > record variable, even though such a function might use a record variable to > hold its result. In both cases the actual row structure is unknown when the > function is written, but for a function returning record the actual > structure is determined when the calling query is parsed, whereas a record > variable can change its row structure on-the-fly. > > > > -- > Adrian Klaver > akla...@comcast.net For this particular case the following works. CREATE OR REPLACE FUNCTION test_function(integer) RETURNS record AS $Body$ DECLARE croid integer; DECLARE R RECORD; BEGIN SELECT INTO croid 2; SELECT INTO R croid,$1; RETURN R; END; $Body$ LANGUAGE plpgsql; -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FUNCTION problem
On Friday 03 April 2009 6:51:05 am Adrian Klaver wrote: > On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: > > Now I remember. Its something that trips me up, the RECORD in RETURN > > setof RECORD is not the same thing as the RECORD in DECLARE RECORD. See > > below for a better explanation- > > http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html# > >PL PGSQL-DECLARATION-RECORDS Note that RECORD is not a true data type, > > only a placeholder. One should also realize that when a PL/pgSQL function > > is declared to return type record, this is not quite the same concept as > > a record variable, even though such a function might use a record > > variable to hold its result. In both cases the actual row structure is > > unknown when the function is written, but for a function returning record > > the actual structure is determined when the calling query is parsed, > > whereas a record variable can change its row structure on-the-fly. > > > > > > > > -- > > Adrian Klaver > > akla...@comcast.net > > For this particular case the following works. > > CREATE OR REPLACE FUNCTION test_function(integer) RETURNS record > AS $Body$ > DECLARE croid integer; > DECLARE R RECORD; > BEGIN > SELECT INTO croid 2; > SELECT INTO R croid,$1; > RETURN R; > END; > > $Body$ > LANGUAGE plpgsql; > > -- > Adrian Klaver > akla...@comcast.net Forgot to show how to call it. test=# SELECT * from test_function(1) as test(c1 int,c2 int); c1 | c2 + 2 | 1 (1 row) -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FUNCTION problem
Adrian Klaver wrote: On Friday 03 April 2009 6:51:05 am Adrian Klaver wrote: On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: Now I remember. Its something that trips me up, the RECORD in RETURN setof RECORD is not the same thing as the RECORD in DECLARE RECORD. See below for a better explanation- http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html# PL PGSQL-DECLARATION-RECORDS Note that RECORD is not a true data type, only a placeholder. One should also realize that when a PL/pgSQL function is declared to return type record, this is not quite the same concept as a record variable, even though such a function might use a record variable to hold its result. In both cases the actual row structure is unknown when the function is written, but for a function returning record the actual structure is determined when the calling query is parsed, whereas a record variable can change its row structure on-the-fly. -- Adrian Klaver akla...@comcast.net For this particular case the following works. CREATE OR REPLACE FUNCTION test_function(integer) RETURNS record AS $Body$ DECLARE croid integer; DECLARE R RECORD; BEGIN SELECT INTO croid 2; SELECT INTO R croid,$1; RETURN R; END; $Body$ LANGUAGE plpgsql; -- Adrian Klaver akla...@comcast.net Forgot to show how to call it. test=# SELECT * from test_function(1) as test(c1 int,c2 int); c1 | c2 + 2 | 1 (1 row) Ah!, I see what you mean about the definition of 'RECORD'. (The lights come on...) And here I thought it would all be so simple. You show a valid, and most informative solution. This should get things working for me. Thank you very much for your help. Peter -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FUNCTION problem
- "Peter Willis" wrote: > Adrian Klaver wrote: > > On Friday 03 April 2009 6:51:05 am Adrian Klaver wrote: > >> On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: > >>> Now I remember. Its something that trips me up, the RECORD in > RETURN > >>> setof RECORD is not the same thing as the RECORD in DECLARE > RECORD. See > >>> below for a better explanation- > >>> > http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html# > >>> PL PGSQL-DECLARATION-RECORDS Note that RECORD is not a true data > type, > >>> only a placeholder. One should also realize that when a PL/pgSQL > function > >>> is declared to return type record, this is not quite the same > concept as > >>> a record variable, even though such a function might use a record > >>> variable to hold its result. In both cases the actual row > structure is > >>> unknown when the function is written, but for a function returning > record > >>> the actual structure is determined when the calling query is > parsed, > >>> whereas a record variable can change its row structure > on-the-fly. > >>> > >>> > >>> > >>> -- > >>> Adrian Klaver > >>> akla...@comcast.net > >> For this particular case the following works. > >> > >> CREATE OR REPLACE FUNCTION test_function(integer) RETURNS record > >> AS $Body$ > >> DECLARE croid integer; > >> DECLARE R RECORD; > >> BEGIN > >>SELECT INTO croid 2; > >>SELECT INTO R croid,$1; > >> RETURN R; > >> END; > >> > >> $Body$ > >> LANGUAGE plpgsql; > >> > >> -- > >> Adrian Klaver > >> akla...@comcast.net > > > > Forgot to show how to call it. > > > > test=# SELECT * from test_function(1) as test(c1 int,c2 int); > > c1 | c2 > > + > > 2 | 1 > > (1 row) > > > > > > Ah!, I see what you mean about the definition of 'RECORD'. > (The lights come on...) > > And here I thought it would all be so simple. > > You show a valid, and most informative solution. > This should get things working for me. If you are using Postgres 8.1+ then it becomes even easier because you can use OUT parameters in the function argument list to eliminate the "as test(c1 int,c2 int)" clause. At this point it becomes a A-->B-->C problem i.e determine what your inputs are, how you want to process them and how you want to return the output. > > Thank you very much for your help. > > Peter Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Performance problem with row count trigger
Greg Sabino Mullane wrote: > A few things spring to mind: > > 1) Use a separate table, rather than storing things inside of > dataset itself. This will reduce the activity on the dataset table. A separate table just for that one column? Would that really help, given that I'd have to add the foreign key dataset_id to the related table? How does splitting activity across dataset and, say, dataset_counts help things? > 2) Do you really need bigint for the counts? Probably not. Still, changing to INTEGER hasn't changed the performance in any measurable way. > 3) If you do want to do this, you'll need a different approach as > Tom mentioned. One way to do this is to have a special method for > bulk loading, that gets around the normal updates and requires that > the user take responsiblity for knowing when and how to call the > alternate path. The basic scheme is this: > > 1. Disable the normal triggers > 2. Enable special (perl) triggers that keep the count in memory > 3. Do the bulk changes > 4. Enable normal triggers, disable special perl one > 5. Do other things as needed > 6. Commit the changes to the assoc_count field. I gave this a shot, and my initial testing looks very promising. Using your scheme, the performance looks to be just as good as without the trigger. I haven't done any kind of edge case testing to see if weird things happen when multiple datasets are added simultaneously, or when someone inserts an assoc record out-of-band while a bulk dataset load is happening, but you've certainly got me well on my way to a workable solution. Many thanks! There's one part I don't get, though... > Number 6 can be done anytime, as long as you are in the same session. The > danger > is in leaving the session without calling the final function. This can be > solved with some deferred FK trickery, or by careful scripting of the events. > All this doesn't completely remove the pain, but it may shift it around enough > in useful ways for your app. I'm not sure I understand the danger you're talking about here. Doesn't putting the whole start_bulkload_assoc_count(), bulk insert, and end_bulkload_assoc_count() process in a transaction save me from any of these problems? Or is there some more subtle problem I'm not seeing? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FUNCTION problem
Adrian Klaver wrote: If you are using Postgres 8.1+ then it becomes even easier because you can use OUT parameters in the function argument list to eliminate the "as test(c1 int,c2 int)" clause. At this point it becomes a A-->B-->C problem i.e determine what your inputs are, how you want to process them and how you want to return the output. '8.1+'?? Hmmm, I'm using 8.3. I could use that. I got the more complex version of the query to work by backing away from 'plpgsql' as the language and using 'sql' instead. I then nested (terribly ugly) my select statements to generate a single SQL query from all. This allows me to change the output of the query without needing to define a new set of output 'OUT' parameters each time I change things. I have use of the 'OUT' parameters with another set of functions though. Thanks for that. Peter -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] pl/pgsql or control structures outside of a function?
Hi all,
Is there any way to use PL/pgSQL code outside of a function?
The reason I'm asking is that I'm porting some code from
sybase/isql/SQR, and it allows some control code structures to be used
in an sql script. For instance,
begin
if ((select count(*) from users where login = 'foo') = 0)
begin
print 'Login "foo" does not exist.'
end
else
begin
print 'Adding account for "foo".'
insert into accounts values ('foo', 'bar')
end
end
PL/pgSQL looks like it would make this port rather easy, but all the
docs and examples I found never had an example of PL/pgSQL outside of
a function. For the purposes of this port I'd really prefer not to
create functions for all this. I searched through the PL/pgSQL docs
and even several Google searches but couldn't find a definitive
answer. It's fine if the answer is no, I'm just curious if I should
pursue this path or look for a different one. And if there's a way to
do this or something like it besides "create scripts in
Perl/Python/etc." that you know of, I'd appreciate any information.
Thanks,
Peter
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pl/pgsql or control structures outside of a function?
On Apr 3, 2009, at 5:03 PM, Peter Koczan wrote: Is there any way to use PL/pgSQL code outside of a function? No. The reason I'm asking is that I'm porting some code from sybase/isql/SQR, and it allows some control code structures to be used in an sql script. For instance, CASE might work for you. http://www.postgresql.org/docs/8.3/interactive/functions-conditional.html John DeSoi, Ph.D. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
