Re: [SQL] Is this possible?
On Mon, Feb 16, 2009 at 7:36 PM, johnf wrote: > Hi, > I'm not to sure this is possible. > > I need to replace a primary key (pkid) with the value of a different field. > I have > pkid = 200 > attendid = 301 > > I need the pkid = 301 > > But there may or may not be a pkid that already exist that has the value of > 301. The attendid is unique and the pkid data type is serial (has a > sequence). If the FK is on update cascade just update it. and setval() the sequence to be max(pkid)+1. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Stored Procedure Generator?
Hi, I have written a stored procedure generator in PgSQL that will generate stored procedures around a table to perform: Insert; update; retrieve all; retrieve by primary key. I would like to know if there is use for such a utility and/or if I am re-inventing the wheel? Also if there is a use for such a utility I would like it to be reviewed/evaluated? Thanks a lot guys. -- "If you would take, you must first give, this is the beginning of intelligence" -- Lao Tze, Tao Te Ching Steve L. Nyemba -- 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] Is this possible?
On Monday 16 February 2009 10:32:26 pm A. Kretschmer wrote:
> In response to johnf :
> > Hi,
> > I'm not to sure this is possible.
> >
> > I need to replace a primary key (pkid) with the value of a different
> > field. I have
> > pkid = 200
> > attendid = 301
> >
> > I need the pkid = 301
> >
> > But there may or may not be a pkid that already exist that has the value
> > of 301. The attendid is unique and the pkid data type is serial (has a
> > sequence).
> >
> > Thanks for the help.
>
> You can do that within a transaction and dropping the pk-constraint:
>
> test=*# \d foo
> Table "public.foo"
> Column | Type |Modifiers
> +-+--
> id | integer | not null default nextval('foo_id_seq'::regclass)
> i | integer |
> Indexes:
> "foo_pkey" PRIMARY KEY, btree (id)
> "foo_i_key" UNIQUE, btree (i)
>
> test=*# select * from foo;
> id | i
> +---
> 1 | 2
> 2 | 1
> 3 | 3
> (3 rows)
>
> test=*# alter table foo drop constraint foo_pkey;
> ALTER TABLE
> test=*# update foo set id=i;
> UPDATE 3
> test=*# alter table foo add primary key(id);
> NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
> "foo_pkey" for table "foo" ALTER TABLE
> test=*# \d foo
> Table "public.foo"
> Column | Type |Modifiers
> +-+--
> id | integer | not null default nextval('foo_id_seq'::regclass)
> i | integer |
> Indexes:
> "foo_pkey" PRIMARY KEY, btree (id)
> "foo_i_key" UNIQUE, btree (i)
>
> test=*# select * from foo;
> id | i
> +---
> 2 | 2
> 1 | 1
> 3 | 3
> (3 rows)
>
>
> HTH, Andreas
Wow that looks like it will work - thanks.
When you say 'within a transaction' do you mean starting with
"Begin" and using "commit"?
--
John Fabiani
--
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] Is this possible?
In response to johnf : > Wow that looks like it will work - thanks. > When you say 'within a transaction' do you mean starting with > "Begin" and using "commit"? Exactly. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How to find number of seconds between 2 timestamps
I am trying to calculate a rate per second and am having trouble getting the
number of seconds between the two timestamps to use in the formula.
overview=> select extract(epoch from interval '1 day'::interval);
date_part
---
86400
(1 row)
overview=> select extract(epoch from interval ('2009-02-16
22:15:28.034567-06'::timestamp with time zone - '2009-02-15
22:15:28.034567-06'::timestamp with time zone));
ERROR: syntax error at or near "'2009-02-16 22:15:28.034567-06'"
LINE 1: select extract(epoch from interval ('2009-02-16 22:15:28.034...
Any suggestions would be helpful,
Thanks,
Woody
iGLASS Networks
3300 Green Level Rd. West
Cary NC 27519
(919) 387-3550 x813
www.iglass.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] How to find number of seconds between 2 timestamps
Woody Woodring wrote:
> I am trying to calculate a rate per second and am having trouble getting the
> number of seconds between the two timestamps to use in the formula.
>
> overview=> select extract(epoch from interval '1 day'::interval);
> date_part
> ---
> 86400
> (1 row)
>
> overview=> select extract(epoch from interval ('2009-02-16
> 22:15:28.034567-06'::timestamp with time zone - '2009-02-15
> 22:15:28.034567-06'::timestamp with time zone));
> ERROR: syntax error at or near "'2009-02-16 22:15:28.034567-06'"
The problem is with "interval (...)" - you can say "interval 'constant'"
or "(expression)::interval".
In your case, since you are subtracting two timestamptz types you will
automatically get an interval type. So, you don't need to do anything:
select extract(epoch from ('2009-02-16 22:15:28.034567-06'::timestamp
with time zone - '2009-02-15 22:15:28.034567-06'::timestamp with time
zone));
date_part
---
86400
(1 row)
HTH
--
Richard Huxton
Archonet Ltd
--
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] Funtion to clean up strings?
Hi
Thanks, that really works :)
Now a last extension.
Some numbers were entered in a "110% perfect" way with an excessive (0).
+49 (0) 123 / 456 789
I have to suspect the source liked to express that it's either +49 or
0 if the +49 isn't applicable, but not both.
Both together are semantically wrong and your function results therefore
to "00123456789".
Correct was "0123456789" or e.g. "+33123456789" if it were an
international number.
This (0) should be silently dropped as long as the endresult has at
least one 0 or + like in the allready covered cases.
I tried to use this RegEx magic myself as far as I could figure it out,
yet and came up with replacing every p in your solution with another regex
case
when regexp_replace(p, E'[^0-9+]', '', 'g') ~ E'^(\\+|00)49'
then '0'||
regexp_replace(
regexp_replace(
regexp_replace(p, E'[^0-9+()]', '', 'g')
, '\\(0\\)||\\(||\\)', '', 'g')
, E'^(?:\\+|00)49(.*)', E'\\1')
when regexp_replace(p, E'[^0-9+]', '', 'g') ~ E'^(\\+|00)'
then '+'||
regexp_replace(
regexp_replace(
regexp_replace(p, E'[^0-9+()]', '', 'g')
, '\\(0\\)||\\(||\\)', '', 'g')
, E'^(?:\\+||00)(.*)', E'\\1')
else
regexp_replace(p, E'[^0-9]', '', 'g')
end
That would catch the leading spaces in " 00 49 ( 0 ) 1 2 3 456 -0", too.
Creating a sql-function thows a WARNING: nonstandard use of \\ in a
string literal
but it still works. Do you know a better or more correct way to reach
the same?
Perhaps one could find a way with less calls to regexp_replace ?
Regards
Andreas :)
Raj Mathur wrote:
On Friday 13 Feb 2009, Andreas wrote:
now ... lets get more complicated.
Phone numbers are entered:
0123/4567-89 national number
0049/123/4567-89 the same number
+49/123/4567-89 still the same number
should come out as 0123456789 to search in this column.
"0049" and "+49" --> 0
while international numbers
+33/123456789
0033/123456789
should come as
+33123456789
TEST=> create table foo(p text);
TEST=> insert into foo (select regexp_split_to_table('0123/4567-89
0049/123/4567-89 +49/123/4567-89 +33/123456789 0033/123456789',' '));
TEST=> select * from foo;
p
--
0123/4567-89
0049/123/4567-89
+49/123/4567-89
+33/123456789
0033/123456789
(5 rows)
TEST=> select
(case
when p ~ E'^(\\+|00)49'
then '0'||regexp_replace(regexp_replace(p, E'[^0-9+]', '', 'g'),
E'^(?:\\+|00)49(.*)', E'\\1')
when p ~ E'^(\\+|00)'
then '+'||regexp_replace(regexp_replace(p, E'[^0-9+]', '', 'g'),
E'^(?:\\+||00)(.*)', E'\\1')
else
regexp_replace(p, E'[^0-9]', '', 'g')
end)
from foo;
regexp_replace
0123456789
0123456789
0123456789
+33123456789
+33123456789
(5 rows)
That do what you want? (Apologies for the wrapped lines.)
Regards,
-- Raju
--
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] Funtion to clean up strings?
On Tuesday 17 Feb 2009, Andreas wrote: > [snip] > case > when regexp_replace(p, E'[^0-9+]', '', 'g') ~ E'^(\\+|00)49' > then '0'|| >regexp_replace( > regexp_replace( > regexp_replace(p, E'[^0-9+()]', '', 'g') > , '\\(0\\)||\\(||\\)', '', 'g') >, E'^(?:\\+|00)49(.*)', E'\\1') > when regexp_replace(p, E'[^0-9+]', '', 'g') ~ E'^(\\+|00)' > then '+'|| >regexp_replace( > regexp_replace( > regexp_replace(p, E'[^0-9+()]', '', 'g') > , '\\(0\\)||\\(||\\)', '', 'g') >, E'^(?:\\+||00)(.*)', E'\\1') > else > regexp_replace(p, E'[^0-9]', '', 'g') > end > > That would catch the leading spaces in " 00 49 ( 0 ) 1 2 3 456 -0", > too. Creating a sql-function thows a WARNING: nonstandard use of \\ > in a string literal > but it still works. Do you know a better or more correct way to reach > the same? > > Perhaps one could find a way with less calls to regexp_replace ? That is what I would have tried too :) The only improvement I can think of is to replace one instance of regex_replace with a string replace, since the string (0) is fixed. On the other hand, I'm not an expert at Pg functions by any means, so someone else may have a faster or more elegant solution. Regards, -- Raju -- Raj Mathurr...@kandalaya.org http://kandalaya.org/ GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves -- 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] Funtion to clean up strings?
string_replace would only replace one searchstring at a time. In this case I need to replace 3 : (0) ( ) because there could be some braces not just as (0) since the innermost replace spares braces regardless where they are. Could one express the following in one expression 1) remove all spaces 2) remove everything but 0-9+ or the first occurance (0) Leading spaces had to be removed anyway because the distract the CASE ... WHEN when it checks for "+49..." but gets " +49...". Then I could use string_replace at the end. Do you know the correct way to code the backslashes to avoid the Warning? Regards Andreas Raj Mathur schrieb: On Tuesday 17 Feb 2009, Andreas wrote: [snip] case when regexp_replace(p, E'[^0-9+]', '', 'g') ~ E'^(\\+|00)49' then '0'|| regexp_replace( regexp_replace( regexp_replace(p, E'[^0-9+()]', '', 'g') , '\\(0\\)||\\(||\\)', '', 'g') , E'^(?:\\+|00)49(.*)', E'\\1') when regexp_replace(p, E'[^0-9+]', '', 'g') ~ E'^(\\+|00)' then '+'|| regexp_replace( regexp_replace( regexp_replace(p, E'[^0-9+()]', '', 'g') , '\\(0\\)||\\(||\\)', '', 'g') , E'^(?:\\+||00)(.*)', E'\\1') else regexp_replace(p, E'[^0-9]', '', 'g') end That would catch the leading spaces in " 00 49 ( 0 ) 1 2 3 456 -0", too. Creating a sql-function thows a WARNING: nonstandard use of \\ in a string literal but it still works. Do you know a better or more correct way to reach the same? Perhaps one could find a way with less calls to regexp_replace ? That is what I would have tried too :) The only improvement I can think of is to replace one instance of regex_replace with a string replace, since the string (0) is fixed. On the other hand, I'm not an expert at Pg functions by any means, so someone else may have a faster or more elegant solution. Regards, -- Raju -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
