[SQL] perlu: did I find a bug, or did I make one?
Situation:
I'm writing a function that fetches data in an Oracle database and stores it in
postgresql database. The function works, but I can't seem to get the error
handling right. I get something but it's not what I expect. This is what I get:
executing 14 generated 4 errors
ERROR: lil foutje Address Belgium
ERROR: lil foutje Address Belgium
ERROR: lil foutje Address Belgium
ERROR: lil foutje Address Belgium
And this is what I expect to get:
executing 14 generated 4 errors
ERROR: lil foutje Address Belgium
ERROR: lil foutje Address France
ERROR: bol nog een foutje Italie
ERROR: bol nog een foutje Beglie
This is the data in Oracle
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values
('national', 'This country', 'ash', 30);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values
('be', 'Address Belgium', 'lil', 501);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values
('fr', 'Address France', 'lil', 496);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values
('it', 'Italie', 'bol', 3138);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values
('national', 'National', '012', 687181679);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values
('internatio', 'International countries', 'ash', 29);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values
('be', 'Beglie', 'bol', 3187);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values
('sp', 'Address Spain', 'bar', 1302174);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values
('internatio', 'International countries', 'as0', 29);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values
('national', 'This country', 'as0', 30);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values
('national', 'National', '011', 216774985);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values
('internatio', 'International', '011', 216774984);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values
('national', 'National', 'hlm', 451094066);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values
('internatio', 'International', 'hlm', 451094067);
This is the target table definition in PostgreSQL
CREATE TABLE public.afh_test (
addrformat VARCHAR(10) NOT NULL,
name VARCHAR(30) NOT NULL,
dataareaid VARCHAR(3) NOT NULL,
recid NUMERIC(10,0) NOT NULL
) WITHOUT OIDS;
CREATE UNIQUE INDEX afh_test_idx ON public.afh_test
USING btree (addrformat, dataareaid);
CREATE TRIGGER afh_test_tr BEFORE INSERT
ON public.afh_test FOR EACH ROW
EXECUTE PROCEDURE public.temp_func1();
CREATE OR REPLACE FUNCTION public.temp_func1 () RETURNS trigger AS
$body$
BEGIN
IF NEW.dataareaid = 'lil' THEN
RAISE EXCEPTION '% foutje %', NEW.dataareaid, NEW.name;
elsIF NEW.dataareaid = 'bol' THEN
RAISE EXCEPTION '% nog een foutje %', NEW.dataareaid, NEW.name;
END IF;
RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
This is the function that retrieves the Oracle data and inserts it in the
target table
CREATE OR REPLACE FUNCTION public.dbi_insert3 () RETURNS integer AS
$body$
use DBI;
$query = 'SELECT * FROM AddressFormatHeading';
$target = 'INSERT INTO afh_test (addrformat, name, dataareaid, recid) VALUES
(?,?,?,?)';
my $dbh_ora =
DBI-connect('dbi:Oracle:database=bmssa;host=firev120-1.indicator.be;sid=mars',
'bmssa', '8QD6ibmD')
or die Couldn't connect to database: . DBI-errstr;
my $dbh_pg =
DBI-connect('dbi:Pg:dbname=defrevdev;host=10.100.1.21;port=2345', 'defrevsys',
'Y2I6vbEW')
or die Couldn't connect to database: . DBI-errstr;
my $sel = $dbh_ora-prepare($query)
or elog(ERROR, Couldn't prepare statement: . $dbh_ora-errstr);
$sel-execute;
my $ins = $dbh_pg-prepare($target)
or elog(ERROR, Couldn't prepare statement: . $dbh_pg-errstr);
my $fetch_tuple_sub = sub { $sel-fetchrow_arrayref };
my @tuple_status;
my $rc = $ins-execute_for_fetch($fetch_tuple_sub, [EMAIL PROTECTED]);
if (DBI-err) {
elog(INFO, DBI-errstr.\n);
my @errors = grep { ref $_ } @tuple_status;
foreach my $error (@errors) {
elog(INFO, $error-[1]);
}
}
$dbh_ora-disconnect;
$dbh_pg-disconnect;
$body$
LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
And this ... well you can guess...
select dbi_insert3();
Thanks for any help!
Re: [SQL] Versionning (was: Whole-row comparison)
Hi Andrew, what is worrying me is that if I use a SRF, any additional WHERE condition would not be taken into account before executing the underlying query, e.g., in this request using a view, the WHERE condition would be considered in the final query : UPDATE params SET version = ver_id; SELECT * FROM bsc_list_view WHERE obj_id = 'xxx'; because the bsc_list_view would be expanded to the underlying request, while using a SRF, the whole table would be scaned before the WHERE condition is applied: SELECT * FROM bsc_list_srf(ver_id) WHERE obj_id = 'xxx'; This is what I mean when I say that the optimization would be lost when using a SRF. Now what is the Right Thing To Do in this particular case ? The nicest thing would really to have parametrized view. Is there any fundamental reason why such a beast does not exist, or is it only postgres (compared to higher-level RDBMS) ? Thanks a lot ! Christian -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of ext Andrew Sullivan Sent: Friday, June 01, 2007 18:51 To: pgsql-sql@postgresql.org Subject: Re: [SQL] Versionning (was: Whole-row comparison) Yes, but I don't think it's true. Because you change the value of ver_id all the time, the actual result can't be collapsed to a constant, so you end up having to execute the query with the additional value, and you still have to plan that. The same thing is true of a function, which will have its plan prepared the first time you execute it. (I could be wrong about this; I suppose the only way would be to try it.) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Jumping Weekends
Hello, (sorry for my poor english) It's my first post here, and my doubt is very simple (I guess). I have a function to populate a table, into WHILE I have the follow piece of code: --Jump Weekend IF (SELECT TO_CHAR(CAST(PRODUCTION_DATE as date),'Day')) = 'Saturday' THEN PRODUCTION_DATE := PRODUCTION_DATE + 2; END IF; It's to jump weekend adding 2 days on the variable PRODUCTION_DATE, the type of the variable is DATE. But, I don't know why, it's doesn't work properly, it's doesn't jump weekends, it's continues normally, someone knows what's happen or what am I doing wrong? I appreciate any help. Thanks Ranieri Mazili ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Jumping Weekends
Hello, you forgot on sunday. Your solution can work, but isn't too efective you can do: production_date := production_date + CASE extract(dow from production_date) WHEN 0 THEN 1 -- sunday WHEN 6 THEN 2 -- saturday ELSE 0 END; there isn't slower string comparation and it's one sql statement without two. Regards Pavel Stehule 2007/6/4, Ranieri Mazili [EMAIL PROTECTED]: Hello, (sorry for my poor english) It's my first post here, and my doubt is very simple (I guess). I have a function to populate a table, into WHILE I have the follow piece of code: --Jump Weekend IF (SELECT TO_CHAR(CAST(PRODUCTION_DATE as date),'Day')) = 'Saturday' THEN PRODUCTION_DATE := PRODUCTION_DATE + 2; END IF; It's to jump weekend adding 2 days on the variable PRODUCTION_DATE, the type of the variable is DATE. But, I don't know why, it's doesn't work properly, it's doesn't jump weekends, it's continues normally, someone knows what's happen or what am I doing wrong? I appreciate any help. Thanks Ranieri Mazili ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(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] Versionning (was: Whole-row comparison)
On Mon, Jun 04, 2007 at 01:40:18PM +0300, [EMAIL PROTECTED] wrote: case ? The nicest thing would really to have parametrized view. Is there any fundamental reason why such a beast does not exist, or is it only postgres (compared to higher-level RDBMS) ? I don't think there's a fundamental reason, no. But why couldn't you change your query to issue the SRF directly, with the parameter: SELECT * FROM some_srf(param1, param2)? A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] perlu: did I find a bug, or did I make one?
Bart Degryse [EMAIL PROTECTED] writes: CREATE TRIGGER afh_test_tr BEFORE INSERT ON public.afh_test FOR EACH ROW EXECUTE PROCEDURE public.temp_func1(); CREATE OR REPLACE FUNCTION public.temp_func1 () RETURNS trigger AS $body$ BEGIN IF NEW.dataareaid =3D 'lil' THEN RAISE EXCEPTION '% foutje %', NEW.dataareaid, NEW.name; elsIF NEW.dataareaid =3D 'bol' THEN RAISE EXCEPTION '% nog een foutje %', NEW.dataareaid, NEW.name; END IF; RETURN NULL; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; You probably don't want this trigger doing RETURN NULL; that's turning all your inserts into no-ops. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] perlu: did I find a bug, or did I make one?
Well, actually I do. If there's any error, I want nothing done. But my real point was that although there are 2 records in my source table with dataareaid = 'lil' and two with dataareaid = 'bol' I still get 4 times the 'lil' error message, while I was expecting 2 times the 'lil' error message and two times the 'bol' error message. Tom Lane [EMAIL PROTECTED] 2007-06-04 16:52 Bart Degryse [EMAIL PROTECTED] writes: CREATE TRIGGER afh_test_tr BEFORE INSERT ON public.afh_test FOR EACH ROW EXECUTE PROCEDURE public.temp_func1(); CREATE OR REPLACE FUNCTION public.temp_func1 () RETURNS trigger AS $body$ BEGIN IF NEW.dataareaid =3D 'lil' THEN RAISE EXCEPTION '% foutje %', NEW.dataareaid, NEW.name; elsIF NEW.dataareaid =3D 'bol' THEN RAISE EXCEPTION '% nog een foutje %', NEW.dataareaid, NEW.name; END IF; RETURN NULL; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; You probably don't want this trigger doing RETURN NULL; that's turning all your inserts into no-ops. regards, tom lane
[SQL] current_date / datetime stuff
Hello, I was hoping someone here may be able to help me out with this one: Is there anything similiar to: SELECT current_date; that will return the date of the first Monday of the month? Please let me know. Thanks, Joshua ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] current_date / datetime stuff
On Mon, Jun 04, 2007 at 10:04:37AM -0500, Joshua wrote: that will return the date of the first Monday of the month? I guess you need to write a function to do this. I suppose you could do it by finding out what day of the week it is and what the date is, then counting backwards to the earliest possible Monday. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] current_date / datetime stuff
On Jun 4, 2007, at 10:27 , Andrew Sullivan wrote:
On Mon, Jun 04, 2007 at 10:04:37AM -0500, Joshua wrote:
that will return the date of the first Monday of the month?
I guess you need to write a function to do this. I suppose you could
do it by finding out what day of the week it is and what the date is,
then counting backwards to the earliest possible Monday.
As Andrew said, there's no built-in function to do this, but it's
easy enough to write one. Here's a rough example (very lightly tested
and probably overly complicated)
CREATE OR REPLACE FUNCTION first_dow(DATE, INTEGER)
RETURNS DATE
IMMUTABLE
LANGUAGE SQL AS $_$
SELECT v_first_day_of_month +
CASE WHEN v_day_of_week = $2 THEN $2 - v_day_of_week
ELSE 8 - v_day_of_week
END AS first_day_of_month
FROM (
SELECT v_first_day_of_month
, extract('dow' from v_first_day_of_month)::integer
AS v_day_of_week
FROM (SELECT date_trunc('month', $1)::date)
AS mon(v_first_day_of_month)) as calc;
$_$;
CREATE OR REPLACE FUNCTION first_monday(DATE)
RETURNS DATE
IMMUTABLE
LANGUAGE SQL AS $_$
SELECT first_dow($1, 1);
$_$;
select first_monday(current_date);
first_monday
--
2007-06-04
(1 row)
select first_monday('2007-04-01');
first_monday
--
2007-04-02
(1 row)
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] current_date / datetime stuff
On 6/4/07, Joshua [EMAIL PROTECTED] wrote:
Hello,
I was hoping someone here may be able to help me out with this one:
Is there anything similiar to: SELECT current_date;
that will return the date of the first Monday of the month?
Please let me know.
Thanks,
Joshua
select (
select
case i = dow
when true then d + (i - dow + 7)
when false then d + (i - dow)
end
from (
select d
, extract(dow from d)::int as dow
, 1 as i -- monday
from (
select date_trunc('month',current_date)::date - 1 as d
) q
) q2
) as first_monday_of_the_month
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] current_date / datetime stuff
On Jun 4, 2007, at 10:59 , Michael Glaesemann wrote:
On Jun 4, 2007, at 10:27 , Andrew Sullivan wrote:
On Mon, Jun 04, 2007 at 10:04:37AM -0500, Joshua wrote:
that will return the date of the first Monday of the month?
I guess you need to write a function to do this. I suppose you could
do it by finding out what day of the week it is and what the date is,
then counting backwards to the earliest possible Monday.
As Andrew said, there's no built-in function to do this, but it's
easy enough to write one. Here's a rough example (very lightly
tested and probably overly complicated)
And a little simpler:
CREATE OR REPLACE FUNCTION first_dow_of_month(DATE, INTEGER)
RETURNS DATE
IMMUTABLE
LANGUAGE SQL AS $_$
SELECT v_first_day_of_month + ( 7 + $2 - v_day_of_week) % 7
AS first_dow_of_month
FROM (
SELECT v_first_day_of_month
, extract('dow' from v_first_day_of_month)::integer
AS v_day_of_week
FROM (SELECT date_trunc('month', $1)::date)
AS mon(v_first_day_of_month)) as calc;
$_$;
CREATE OR REPLACE FUNCTION first_monday(DATE)
RETURNS DATE
IMMUTABLE
LANGUAGE SQL AS $_$
SELECT first_dow_of_month($1, 1);
$_$;
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] Encrypted column
Hello, I need to store users and passwords on a table and I want to store it encrypted, but I don't found documentation about it, how can I create a table with columns user and password with column password encrypted and how can I check if user and password are correct using a sql query ? I appreciate any help Thanks Ranieri Mazili ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Encrypted column
I need to store users and passwords on a table and I want to store it encrypted, but I don't found documentation about it, how can I create a Take a look at the pgcrypto user-contributed module. -- Gary Chambers // Nothing fancy and nothing Microsoft! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Inserting a path into Database
Hello,
I need to insert a path into a table, but because \ I have a error by
postgres, so how can I insert a path like bellow into a table:
insert into production values ('C:\Program Files\My program');
I appreciate any help
Thanks
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Inserting a path into Database
If you are on 8.1 you can use double qoutes ( 'C:\\Program Files\\My
program' ) on in 8.2 you can use the new backslash_quote (string)
setting.
You can find help on backslash_quote (string) at --
http://www.postgresql.org/docs/current/static/runtime-config-compatible.html
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 6/4/07, Ranieri Mazili [EMAIL PROTECTED] wrote:
Hello,
I need to insert a path into a table, but because \ I have a error by
postgres, so how can I insert a path like bellow into a table:
insert into production values ('C:\Program Files\My program');
I appreciate any help
Thanks
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] current_date / datetime stuff
oneliner:
select date_trunc('month',now()) + ((8 - extract('dow' from date_trunc
('month',now()))||'days')::text)::interval;
Kristo
On 04.06.2007, at 19:39, Michael Glaesemann wrote:
On Jun 4, 2007, at 10:59 , Michael Glaesemann wrote:
On Jun 4, 2007, at 10:27 , Andrew Sullivan wrote:
On Mon, Jun 04, 2007 at 10:04:37AM -0500, Joshua wrote:
that will return the date of the first Monday of the month?
I guess you need to write a function to do this. I suppose you
could
do it by finding out what day of the week it is and what the date
is,
then counting backwards to the earliest possible Monday.
As Andrew said, there's no built-in function to do this, but it's
easy enough to write one. Here's a rough example (very lightly
tested and probably overly complicated)
And a little simpler:
CREATE OR REPLACE FUNCTION first_dow_of_month(DATE, INTEGER)
RETURNS DATE
IMMUTABLE
LANGUAGE SQL AS $_$
SELECT v_first_day_of_month + ( 7 + $2 - v_day_of_week) % 7
AS first_dow_of_month
FROM (
SELECT v_first_day_of_month
, extract('dow' from v_first_day_of_month)::integer
AS v_day_of_week
FROM (SELECT date_trunc('month', $1)::date)
AS mon(v_first_day_of_month)) as calc;
$_$;
CREATE OR REPLACE FUNCTION first_monday(DATE)
RETURNS DATE
IMMUTABLE
LANGUAGE SQL AS $_$
SELECT first_dow_of_month($1, 1);
$_$;
Michael Glaesemann
grzm seespotcode net
---(end of
broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] [GENERAL] Inserting a path into Database
On Jun 4, 2007, at 15:10 , Ranieri Mazili wrote:
I need to insert a path into a table, but because \ I have a
error by postgres, so how can I insert a path like bellow into a
table:
insert into production values ('C:\Program Files\My program');
In v8.0 and later you can use dollar-quoted strings, e.g.,
select $_$C:\Program Files\My program$_$;
?column?
-
C:\Program Files\My program
http://www.postgresql.org/docs/8.2/interactive/sql-syntax-
lexical.html#SQL-SYNTAX-DOLLAR-QUOTING
For 8.2, you can turn on standard_conforming_strings in
postgresql.conf so \ will be treated literally:
http://www.postgresql.org/docs/8.2/interactive/runtime-config-
compatible.html#GUC-STANDARD-CONFORMING-STRINGS
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
