[SQL] Set binary column dependent on cumulative value of integer column
Hi I've got a bit of a tricky (or me!) problem. The example below is completely ficticious but describes my real problem in a way which might be easier to understand. Imagine your table contains CREATE TABLE passenger_queue ( id serial NOT NULL, name character varying(40) NOT NULL, ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Setting boolean column based on cumulative integer value
Hi (again!) [stupid email program sent my message before I finished it!!!] I've got a bit of a tricky (for me!) problem. The example below is completely ficticious but describes my real problem in a way which might be easier to understand. Imagine the table contains a list of passenger wanting to get on a small(!) plane. The plane can carry at most 200kg of passengers and will be filled strictly on a first-come first-serve basis - well, check-in staff is a bit stupid ;-). So what needs to be done is to set the 'gets_seat' column to true until the weight limit is reached. CREATE TABLE passenger_queue ( id serial NOT NULL, name character varying(40) NOT NULL, weight integer NOT NULL, gets_seat boolean default false ) insert into passenger_queue values (1,Peter,75,false) insert into passenger_queue values (2,Mary,50,false) insert into passenger_queue values (3,John,70,false) insert into passenger_queue values (4,Steve,80,false) According to the specifications given above Peter, Mary and John would have 'gets_seat' set to true because their cumulative weight is 195kg while Steve misses out. The big question is: How can I do this in a nice SQL query??? Thanks Markus ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Grants
Schema grants CREATE SCHEMA base AUTHORIZATION root; GRANT ALL ON SCHEMA base TO root; GRANT USAGE ON SCHEMA base TO administrators; Table grants GRANT ALL ON TABLE base.local TO root; GRANT SELECT, UPDATE, INSERT ON TABLE base.local TO administrators; Still the same problem. :( 2006/11/30, imad [EMAIL PROTECTED]: You did not grant access privileges to schema. Also GRANT administrators on the base schema as you did for the table. --Imad www.EnterpriseDB.com On 12/1/06, Ezequias Rodrigues da Rocha [EMAIL PROTECTED] wrote: Hi list, I am having problem with grants and users on PostgreSQL. I am using pgAdmin to connect like other user to test my permissions. As the owner of the database I have criated two roles: administrators (cannot connect) ezequias (can connect) I give permissions to a table I have: GRANT SELECT, UPDATE, INSERT ON TABLE base.table1 TO administrators; My user: CREATE ROLE ezequias LOGIN NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; GRANT administradores TO ezequias; My group CREATE ROLE administradores NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; When I try to access the table base.table1 with ezequias login the pgAdmin reports: (see attached image) Could someone tell me what I did wrong ? Ezequias ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/ ---(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] Setting boolean column based on cumulative integer value
am Sat, dem 02.12.2006, um 0:50:37 +1100 mailte Markus Juenemann folgendes: I've got a bit of a tricky (for me!) problem. The example below is completely ficticious but describes my real problem in a way which might be easier to understand. Imagine the table contains a list of passenger wanting to get on a small(!) plane. The plane can carry at most 200kg of passengers and will be filled strictly on a first-come first-serve basis - well, check-in staff is a bit stupid ;-). So what needs to be done is to set the 'gets_seat' column to true until the weight limit is reached. With your example, i wrote a little function for this: ---%-- create or replace function check_wight( out id int, out name text, out weight int, out gets_seat boolean ) returns setof record as $$ declare rec record; sum int; begin sum = 0; for rec in select * from passenger_queue order by id LOOP id = rec.id; name = rec.name; weight = rec.weight; sum = sum + weight; if sum 200 then gets_seat='t'::bool; else gets_seat='f'::bool; end if; return next ; end loop; end $$ language plpgsql; ---%-- test=# select * from passenger_queue; id | name | weight | gets_seat +---++--- 1 | Peter | 75 | f 2 | Mary | 50 | f 3 | John | 70 | f 4 | Steve | 80 | f (4 rows) test=# select * from check_wight(); id | name | weight | gets_seat +---++--- 1 | Peter | 75 | t 2 | Mary | 50 | t 3 | John | 70 | t 4 | Steve | 80 | f (4 rows) HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Setting boolean column based on cumulative integer value
CREATE TABLE passenger_queue ( id serial NOT NULL, name character varying(40) NOT NULL, weight integer NOT NULL, gets_seat boolean default false ) insert into passenger_queue values (1,Peter,75,false) insert into passenger_queue values (2,Mary,50,false) insert into passenger_queue values (3,John,70,false) insert into passenger_queue values (4,Steve,80,false) According to the specifications given above Peter, Mary and John would have 'gets_seat' set to true because their cumulative weight is 195kg while Steve misses out. The big question is: How can I do this in a nice SQL query??? Well there are two ways that I can think of: The first option is probably the best. But the second is a good mental exercise. 1) a trigger that checks to insure that a new record doesn't exceed your max. 2) instead of inserting passenger weight you could insert begin/end weight range for each passenger. i.e.: CREATE TABLE passenger_queue ( id serial NOT NULL, name character varying(40) NOT NULL, plane_start_weight integer NOT NULL, plane_end_weight integer not null, constraint plane_max_wieght check( plane_end_weight = 200 ), constraint sanity_check check( plane_end_weight plane_start_weight) ) insert into passenger_queue values (1,Peter, (select max(end_weight) from passenger_queue), (select max(end_weight) from passenger_queue) + 75); insert into passenger_queue values (2,Mary, (select max(end_weight) from passenger_queue), (select max(end_weight) from passenger_queue) + 50); insert into passenger_queue values (3,John, (select max(end_weight) from passenger_queue), (select max(end_weight) from passenger_queue) + 70); insert into passenger_queue values (4,Steve, (select max(end_weight) from passenger_queue), (select max(end_weight) from passenger_queue) + 80); once you try to insert a record that exceeds your max weight the insert will fail. ofcourse if you have to delete a passenger record because he/she wishes to get off early you will need to have an additional update statement to shift down higher valued records insure that the range does not have any gaps. Regards, Richard Broersma Jr. ---(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] Grants
On Friday 01 December 2006 06:03 am, Ezequias Rodrigues da Rocha wrote: 2006/11/30, imad [EMAIL PROTECTED]: You did not grant access privileges to schema. Also GRANT administrators on the base schema as you did for the table. --Imad www.EnterpriseDB.com On 12/1/06, Ezequias Rodrigues da Rocha [EMAIL PROTECTED] wrote: Hi list, I am having problem with grants and users on PostgreSQL. I am using pgAdmin to connect like other user to test my permissions. As the owner of the database I have criated two roles: administrators (cannot connect) ezequias (can connect) I give permissions to a table I have: GRANT SELECT, UPDATE, INSERT ON TABLE base.table1 TO administrators; My user: CREATE ROLE ezequias LOGIN NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; GRANT administradores TO ezequias; My group CREATE ROLE administradores NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; When I try to access the table base.table1 with ezequias login the pgAdmin reports: (see attached image) Could someone tell me what I did wrong ? Ezequias ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Schema grants CREATE SCHEMA base AUTHORIZATION root; GRANT ALL ON SCHEMA base TO root; GRANT USAGE ON SCHEMA base TO administrators; Table grants GRANT ALL ON TABLE base.local TO root; GRANT SELECT, UPDATE, INSERT ON TABLE base.local TO administrators; Still the same problem. :( Two things I see. The first may only be a translation artifact. You have CREATE ROLE administradores and then GRANT to administrators on the table. Second in the CREATE ROLE ezequias you have NOINHERIT. This means ezequias does not automatically assume the privileges of the ROLES it belongs to. To acquire the privileges you have to do a SET ROLE administrators at the connection. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] calling elog possibly causing problem in DirectFunctionCall1
Hi ,
In certain C trigger function following code snippet causes ERROR:
---
elog (NOTICE , before calling DirectFunctionCall1);
data-time_stamp =
DirectFunctionCall1(timestamptz_in, CStringGetDatum(now));
elog (NOTICE , after calling DirectFunctionCall1);
begin work;INSERT INTO audittest.test_table (name,foo) values
('test','test'); rollback;
BEGIN
NOTICE: before calling DirectFunctionCall1
ERROR: timestamp(-1073748880) precision must be between 0 and 6
ROLLBACK
If the elog before DirectFunctionCall1 is removed the code works fine.
begin work;INSERT INTO audittest.test_table (name,foo) values
('test','test'); rollback;
BEGIN
NOTICE: after calling DirectFunctionCall1
INSERT 0 1
ROLLBACK
Can anyone please explain how removing a elog makes a difference?
Regds
Mallah.
Re: [SQL] Autovaccum
Ezequias Rodrigues da Rocha wrote: Could you tell me if only this both options are ok (attach) ? If I don't mark the interval of vacuuns what will be the interval of each vacuum ? The default settings may be fine for you, it depends a bit on how many insert/updates you get in a given time frame. http://www.postgresql.org/docs/8.1/interactive/runtime-config-autovacuum.html will give a definition of each parameter that can be set. http://www.postgresql.org/docs/8.1/interactive/maintenance.html#AUTOVACUUM will explain in more detail. Basically every autovacuum_naptime seconds autovacuum looks at estimates of how much the database has changed since the last run. If the amount of change is greater than the thresholds determined from the other settings then a vacuum/analyze will be done. If you have 100 records added/updated per day then you may only need it to run 1 or 2 times a day using smaller thresholds. If you are adding 10,000 records an hour then you will want it to run more often. 2006/11/30, Shane Ambler [EMAIL PROTECTED]: Alvaro Herrera wrote: Ezequias Rodrigues da Rocha wrote: Hi list, I would like to know if it is necessary to set my database to autovaccum if the intent of my DB Manager is do not make any deletion in any time. If there is no deletions why autovaccum ok ? You need to vacuum from time to time anyway, even if you don't delete anything. The easiest way to do it is let autovacuum do it for you. One thing that vacuum/autovacuum does is mark space used by deleted rows to be reused. Without deletes this won't be necessary in table data files. But when you update a record an index may also be updated and have the same effect within the index storage space. There are other things that vacuum does to keep your database running optimally. One is to update planner statistics about how many rows are in each table which effects the query planning and optimizing. Without deletes a plain vacuum won't achieve a great deal, but a regular VACUUM ANALYZE (as done by autovacuum) will make a difference to the performance of your database. If no data in your db changes then you won't have to bother vacuuming. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(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] calling elog possibly causing problem in DirectFunctionCall1
Rajesh Kumar Mallah [EMAIL PROTECTED] writes: data-time_stamp = DirectFunctionCall1(timestamptz_in, CStringGetDatum(now)); This code is incorrect, as timestamptz_in takes three arguments. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] calling elog possibly causing problem in DirectFunctionCall1
On 12/1/06, Tom Lane [EMAIL PROTECTED] wrote: Rajesh Kumar Mallah [EMAIL PROTECTED] writes: data-time_stamp = DirectFunctionCall1(timestamptz_in, CStringGetDatum(now)); This code is incorrect, as timestamptz_in takes three arguments. Dear Sir, thanks for the kind reply. field time_stamp of data is declared as: Datum time_stamp; it is supposed to be populated with current timestamp , (now()) can you please tell me what should be passed as the third argument? Regds Mallah. regards, tom lane
Re: [SQL] calling elog possibly causing problem in DirectFunctionCall1
On 12/1/06, Tom Lane [EMAIL PROTECTED] wrote: Rajesh Kumar Mallah [EMAIL PROTECTED] writes: data-time_stamp = DirectFunctionCall1(timestamptz_in, CStringGetDatum(now)); This code is incorrect, as timestamptz_in takes three arguments. replaced it with: data-time_stamp = DirectFunctionCall3(timestamptz_in, CStringGetDatum(now), ObjectIdGetDatum(InvalidOid), Int32GetDatum(-1)) now it works fine. (code lifted from contrib/spi/moddatetime.c(line 73) hopefully its correct. regards, tom lane
[SQL] Problem inserting composite type values
Hi all --
(huge apologies if this is a duplicate post -- I sent from an
unsubscribed email account before...)
I have a problem trying to INSERT INTO a table by selecting from a
function that returns a composite type. (I'm running version 8.1.4, FYI)
Basically, I have two tables. I want to retrieve rows from one table and
store them into the other. The schema of the two tables is not the same,
so I use a conversion function (written in plperl) that takes a row from
the start table and returns a row from the end table. However, I can't
get the insert working.
Here's a simplified example of my real system (must have plperl
installed to try it):
---
-- Read rows from here...
CREATE TABLE startTable ( intVal integer, textVal text );
-- ...and store as rows in here
CREATE TABLE endTable ( intVal integer, newVal1 integer, newVal2 integer);
-- Some test data for the startTable
INSERT INTO startTable VALUES ( 1, '10:11');
INSERT INTO startTable VALUES ( 2, '20:25');
INSERT INTO startTable VALUES ( 3, '30:38');
-- Note: Takes composite type as argument, and returns composite type.
-- This just converts a row of startTable into a row of endTable, splitting
-- the colon-delimited integers from textVal into separate integers.
CREATE OR REPLACE FUNCTION convertStartToEnd(startTable) RETURNS
endTable AS $$
my ($startTable) = @_;
my @newVals = split(/:/, $startTable-{textval});
my $result = { intval=$startTable-{intval},
newval1=@newVals[0], newval2=@newVals[1] };
return $result;
$$ LANGUAGE plperl;
---
Now, if I run the following SELECT, I get the results below it:
SELECT convertStartToEnd(st.*) FROM startTable st;
convertstarttoend
---
(1,10,11)
(2,20,25)
(3,30,38)
(3 rows)
This seems OK. But when I try to INSERT the results of this select into
the endTable, I get this error:
INSERT INTO endTable SELECT convertStartToEnd(st.*) FROM startTable st;
ERROR: column intval is of type integer but expression is of type
endtable
HINT: You will need to rewrite or cast the expression
It's taking the entire row coming out of the function (e.g. (3,30,38))
and tries to fit it all into just the first column, intVal. I'm
obviously doing something wrong. Is there some way to format the INSERT
so that I can get full rows inserted, with the individual columns
separated out properly (e.g. intVal=3, newVal1=30, newVal2=38)?
I'm still pretty new to all this, so it could be something simple.
Thanks for reading.
Kind Regards,
Chris Dunworth
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Problem inserting composite type values
On Fri, 1 Dec 2006, Chris Dunworth wrote:
Hi all --
(huge apologies if this is a duplicate post -- I sent from an
unsubscribed email account before...)
I have a problem trying to INSERT INTO a table by selecting from a
function that returns a composite type. (I'm running version 8.1.4, FYI)
Basically, I have two tables. I want to retrieve rows from one table and
store them into the other. The schema of the two tables is not the same,
so I use a conversion function (written in plperl) that takes a row from
the start table and returns a row from the end table. However, I can't
get the insert working.
Here's a simplified example of my real system (must have plperl
installed to try it):
---
-- Read rows from here...
CREATE TABLE startTable ( intVal integer, textVal text );
-- ...and store as rows in here
CREATE TABLE endTable ( intVal integer, newVal1 integer, newVal2 integer);
-- Some test data for the startTable
INSERT INTO startTable VALUES ( 1, '10:11');
INSERT INTO startTable VALUES ( 2, '20:25');
INSERT INTO startTable VALUES ( 3, '30:38');
-- Note: Takes composite type as argument, and returns composite type.
-- This just converts a row of startTable into a row of endTable, splitting
-- the colon-delimited integers from textVal into separate integers.
CREATE OR REPLACE FUNCTION convertStartToEnd(startTable) RETURNS
endTable AS $$
my ($startTable) = @_;
my @newVals = split(/:/, $startTable-{textval});
my $result = { intval=$startTable-{intval},
newval1=@newVals[0], newval2=@newVals[1] };
return $result;
$$ LANGUAGE plperl;
---
Now, if I run the following SELECT, I get the results below it:
SELECT convertStartToEnd(st.*) FROM startTable st;
convertstarttoend
---
(1,10,11)
(2,20,25)
(3,30,38)
(3 rows)
This seems OK. But when I try to INSERT the results of this select into
the endTable, I get this error:
INSERT INTO endTable SELECT convertStartToEnd(st.*) FROM startTable st;
I think you'd need something like
INSERT INTO endTable SELECT (convertStartToEnd(st.*)).* FROM startTable
st;
to make it break up the type into its components.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Setting boolean column based on cumulative integer value
On 12/1/06, Markus Juenemann [EMAIL PROTECTED] wrote: Hi (again!) [stupid email program sent my message before I finished it!!!] I've got a bit of a tricky (for me!) problem. The example below is completely ficticious but describes my real problem in a way which might be easier to understand. Imagine the table contains a list of passenger wanting to get on a small(!) plane. The plane can carry at most 200kg of passengers and will be filled strictly on a first-come first-serve basis - well, check-in staff is a bit stupid ;-). So what needs to be done is to set the 'gets_seat' column to true until the weight limit is reached. CREATE TABLE passenger_queue ( id serial NOT NULL, name character varying(40) NOT NULL, weight integer NOT NULL, gets_seat boolean default false ) insert into passenger_queue values (1,Peter,75,false) insert into passenger_queue values (2,Mary,50,false) insert into passenger_queue values (3,John,70,false) insert into passenger_queue values (4,Steve,80,false) According to the specifications given above Peter, Mary and John would have 'gets_seat' set to true because their cumulative weight is 195kg while Steve misses out. The big question is: How can I do this in a nice SQL query??? I would ditch the gets_seat column and instead create a view that calculates the value when you need it. This helps eliminate redundant data. CREATE VIEW passenger_queue_vw ( id, name, weight, gets_seat ) AS SELECT queue.id, queue.name, queue.weight, CASE sum(others.gets_seat) = 200 FROM passenger_queue queue INNER JOIN passenger_queue others ON ( others.id = queue.id -- There should really be a create date used here -- but for example purposes I assume the id column -- is an increasing sequence ) GROUP BY queue.id, queue.name, queue.weight If you have performance concerns you can create a materialized view. Of course if you don't want the record to even be allowed (cause an error on insert), you should use a constraint as mentioned in one of the other responses to your question. -Aaron -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ==
Re: [SQL] Problem inserting composite type values
On 12/1/06, Stephan Szabo [EMAIL PROTECTED] wrote:
On Fri, 1 Dec 2006, Chris Dunworth wrote:
Hi all --
(huge apologies if this is a duplicate post -- I sent from an
unsubscribed email account before...)
I have a problem trying to INSERT INTO a table by selecting from a
function that returns a composite type. (I'm running version 8.1.4, FYI)
Basically, I have two tables. I want to retrieve rows from one table and
store them into the other. The schema of the two tables is not the same,
so I use a conversion function (written in plperl) that takes a row from
the start table and returns a row from the end table. However, I can't
get the insert working.
Here's a simplified example of my real system (must have plperl
installed to try it):
---
-- Read rows from here...
CREATE TABLE startTable ( intVal integer, textVal text );
-- ...and store as rows in here
CREATE TABLE endTable ( intVal integer, newVal1 integer, newVal2
integer);
-- Some test data for the startTable
INSERT INTO startTable VALUES ( 1, '10:11');
INSERT INTO startTable VALUES ( 2, '20:25');
INSERT INTO startTable VALUES ( 3, '30:38');
-- Note: Takes composite type as argument, and returns composite type.
-- This just converts a row of startTable into a row of endTable,
splitting
-- the colon-delimited integers from textVal into separate integers.
CREATE OR REPLACE FUNCTION convertStartToEnd(startTable) RETURNS
endTable AS $$
my ($startTable) = @_;
my @newVals = split(/:/, $startTable-{textval});
my $result = { intval=$startTable-{intval},
newval1=@newVals[0], newval2=@newVals[1] };
return $result;
$$ LANGUAGE plperl;
---
Now, if I run the following SELECT, I get the results below it:
SELECT convertStartToEnd(st.*) FROM startTable st;
convertstarttoend
---
(1,10,11)
(2,20,25)
(3,30,38)
(3 rows)
This seems OK. But when I try to INSERT the results of this select into
the endTable, I get this error:
INSERT INTO endTable SELECT convertStartToEnd(st.*) FROM startTable st;
I think you'd need something like
INSERT INTO endTable SELECT (convertStartToEnd(st.*)).* FROM startTable
st;
to make it break up the type into its components.
INSERT INTO endTable SELECT * FROM (SELECT convertStartToEnd(st.*) FROM
startTable)
that should work too
--
==
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
Re: [SQL] Problem inserting composite type values
Stephan Szabo wrote:
On Fri, 1 Dec 2006, Chris Dunworth wrote:
Hi all --
(huge apologies if this is a duplicate post -- I sent from an
unsubscribed email account before...)
I have a problem trying to INSERT INTO a table by selecting from a
function that returns a composite type. (I'm running version 8.1.4, FYI)
Basically, I have two tables. I want to retrieve rows from one table and
store them into the other. The schema of the two tables is not the same,
so I use a conversion function (written in plperl) that takes a row from
the start table and returns a row from the end table. However, I can't
get the insert working.
Here's a simplified example of my real system (must have plperl
installed to try it):
---
-- Read rows from here...
CREATE TABLE startTable ( intVal integer, textVal text );
-- ...and store as rows in here
CREATE TABLE endTable ( intVal integer, newVal1 integer, newVal2 integer);
-- Some test data for the startTable
INSERT INTO startTable VALUES ( 1, '10:11');
INSERT INTO startTable VALUES ( 2, '20:25');
INSERT INTO startTable VALUES ( 3, '30:38');
-- Note: Takes composite type as argument, and returns composite type.
-- This just converts a row of startTable into a row of endTable, splitting
-- the colon-delimited integers from textVal into separate integers.
CREATE OR REPLACE FUNCTION convertStartToEnd(startTable) RETURNS
endTable AS $$
my ($startTable) = @_;
my @newVals = split(/:/, $startTable-{textval});
my $result = { intval=$startTable-{intval},
newval1=@newVals[0], newval2=@newVals[1] };
return $result;
$$ LANGUAGE plperl;
---
Now, if I run the following SELECT, I get the results below it:
SELECT convertStartToEnd(st.*) FROM startTable st;
convertstarttoend
---
(1,10,11)
(2,20,25)
(3,30,38)
(3 rows)
This seems OK. But when I try to INSERT the results of this select into
the endTable, I get this error:
INSERT INTO endTable SELECT convertStartToEnd(st.*) FROM startTable st;
I think you'd need something like
INSERT INTO endTable SELECT (convertStartToEnd(st.*)).* FROM startTable
st;
to make it break up the type into its components.
Yes! That was exactly it. I thought it might have been something simple.
Thanks, Stephan!
-Chris
Re: [SQL] Problem inserting composite type values
Aaron Bono wrote:
On 12/1/06, *Stephan Szabo* [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
On Fri, 1 Dec 2006, Chris Dunworth wrote:
Hi all --
(huge apologies if this is a duplicate post -- I sent from an
unsubscribed email account before...)
I have a problem trying to INSERT INTO a table by selecting from a
function that returns a composite type. (I'm running version
8.1.4, FYI)
Basically, I have two tables. I want to retrieve rows from one
table and
store them into the other. The schema of the two tables is not
the same,
so I use a conversion function (written in plperl) that takes a
row from
the start table and returns a row from the end table. However, I
can't
get the insert working.
Here's a simplified example of my real system (must have plperl
installed to try it):
---
-- Read rows from here...
CREATE TABLE startTable ( intVal integer, textVal text );
-- ...and store as rows in here
CREATE TABLE endTable ( intVal integer, newVal1 integer, newVal2
integer);
-- Some test data for the startTable
INSERT INTO startTable VALUES ( 1, '10:11');
INSERT INTO startTable VALUES ( 2, '20:25');
INSERT INTO startTable VALUES ( 3, '30:38');
-- Note: Takes composite type as argument, and returns composite
type.
-- This just converts a row of startTable into a row of
endTable, splitting
-- the colon-delimited integers from textVal into separate integers.
CREATE OR REPLACE FUNCTION convertStartToEnd(startTable) RETURNS
endTable AS $$
my ($startTable) = @_;
my @newVals = split(/:/, $startTable-{textval});
my $result = { intval=$startTable-{intval},
newval1=@newVals[0], newval2=@newVals[1] };
return $result;
$$ LANGUAGE plperl;
---
Now, if I run the following SELECT, I get the results below it:
SELECT convertStartToEnd(st.*) FROM startTable st;
convertstarttoend
---
(1,10,11)
(2,20,25)
(3,30,38)
(3 rows)
This seems OK. But when I try to INSERT the results of this
select into
the endTable, I get this error:
INSERT INTO endTable SELECT convertStartToEnd(st.*) FROM
startTable st;
I think you'd need something like
INSERT INTO endTable SELECT (convertStartToEnd(st.*)).* FROM
startTable
st;
to make it break up the type into its components.
INSERT INTO endTable SELECT * FROM (SELECT convertStartToEnd(st.*)
FROM startTable)
that should work too
Hi Aaron --
I had actually tried your approach earlier, or something very similar:
INSERT INTO endTable SELECT et.* FROM (SELECT convertStartToEnd(st.*)
FROM startTable st) AS et;
Which is basically the same as you suggest, plus an alias (et) for the
subquery that postgresql was asking for. But it gave the same type
mismatch result as I posted about.
Turns out Stephan's suggestion did the trick.
Cheers,
Chris
Re: [SQL] Problem inserting composite type values
On 12/1/06, Chris Dunworth [EMAIL PROTECTED] wrote:
Aaron Bono wrote:
On 12/1/06, Stephan Szabo [EMAIL PROTECTED] wrote:
On Fri, 1 Dec 2006, Chris Dunworth wrote:
Hi all --
(huge apologies if this is a duplicate post -- I sent from an
unsubscribed email account before...)
I have a problem trying to INSERT INTO a table by selecting from a
function that returns a composite type. (I'm running version 8.1.4,
FYI)
Basically, I have two tables. I want to retrieve rows from one table
and
store them into the other. The schema of the two tables is not the
same,
so I use a conversion function (written in plperl) that takes a row
from
the start table and returns a row from the end table. However, I can't
get the insert working.
Here's a simplified example of my real system (must have plperl
installed to try it):
---
-- Read rows from here...
CREATE TABLE startTable ( intVal integer, textVal text );
-- ...and store as rows in here
CREATE TABLE endTable ( intVal integer, newVal1 integer, newVal2
integer);
-- Some test data for the startTable
INSERT INTO startTable VALUES ( 1, '10:11');
INSERT INTO startTable VALUES ( 2, '20:25');
INSERT INTO startTable VALUES ( 3, '30:38');
-- Note: Takes composite type as argument, and returns composite type.
-- This just converts a row of startTable into a row of endTable,
splitting
-- the colon-delimited integers from textVal into separate integers.
CREATE OR REPLACE FUNCTION convertStartToEnd(startTable) RETURNS
endTable AS $$
my ($startTable) = @_;
my @newVals = split(/:/, $startTable-{textval});
my $result = { intval=$startTable-{intval},
newval1=@newVals[0], newval2=@newVals[1] };
return $result;
$$ LANGUAGE plperl;
---
Now, if I run the following SELECT, I get the results below it:
SELECT convertStartToEnd(st.*) FROM startTable st;
convertstarttoend
---
(1,10,11)
(2,20,25)
(3,30,38)
(3 rows)
This seems OK. But when I try to INSERT the results of this select
into
the endTable, I get this error:
INSERT INTO endTable SELECT convertStartToEnd(st.*) FROM startTable
st;
I think you'd need something like
INSERT INTO endTable SELECT (convertStartToEnd(st.*)).* FROM startTable
st;
to make it break up the type into its components.
INSERT INTO endTable SELECT * FROM (SELECT convertStartToEnd(st.*) FROM
startTable)
that should work too
Hi Aaron --
I had actually tried your approach earlier, or something very similar:
INSERT INTO endTable SELECT et.* FROM (SELECT convertStartToEnd(st.*) FROM
startTable st) AS et;
Which is basically the same as you suggest, plus an alias (et) for the
subquery that postgresql was asking for. But it gave the same type mismatch
result as I posted about.
Turns out Stephan's suggestion did the trick.
Good to know. I will keep that in mind if I come across that again in the
future.
-Aaron
--
==
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
[SQL] Tracking Down Error in Stored Procedure
I have a nightly batch that runs a stored procedure/function. This procedure has a habit of erroring on the first of each month and gives the following: ERROR: cannot EXECUTE a null querystring CONTEXT: PL/pgSQL function update_web_site_hits_fn line 200 at execute statement My question is this: What is line 200? Is it the 200th line after the CREATE part of the stored procedure? Or is it the 200th line after the BEGIN? Or maybe something else? I have a possible culprit at both places and don't know which one is the offender. Also, if the EXECUTE is over many lines, is line 200 the first line of the EXECUTE statement or the ending line of the EXECUTE (where the ; is)? I am using PostgreSQL 8.1.3 on CENTOS Linux. Thanks, Aaron -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ==
Re: [SQL] Tracking Down Error in Stored Procedure
Aaron Bono [EMAIL PROTECTED] writes: CONTEXT: PL/pgSQL function update_web_site_hits_fn line 200 at execute statement My question is this: What is line 200? Is it the 200th line after the CREATE part of the stored procedure? Or is it the 200th line after the BEGIN? Or maybe something else? It's the 200th line in the function body string. IIRC there is a special case to not count a leading newline in the body string, so that the counting goes like CREATE FUNCTION foo ... RETURNS ... AS $$ DECLARE -- this is line 1 or without exploiting the special case CREATE FUNCTION foo ... RETURNS ... AS $$DECLARE -- this is line 1 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] Tracking Down Error in Stored Procedure
On 12/2/06, Tom Lane [EMAIL PROTECTED] wrote: Aaron Bono [EMAIL PROTECTED] writes: CONTEXT: PL/pgSQL function update_web_site_hits_fn line 200 at execute statement My question is this: What is line 200? Is it the 200th line after the CREATE part of the stored procedure? Or is it the 200th line after the BEGIN? Or maybe something else? It's the 200th line in the function body string. IIRC there is a special case to not count a leading newline in the body string, so that the counting goes like CREATE FUNCTION foo ... RETURNS ... AS $$ DECLARE -- this is line 1 or without exploiting the special case CREATE FUNCTION foo ... RETURNS ... AS $$DECLARE -- this is line 1 regards, tom lane I got it fixed. Thanks for the help! -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ==
Re: [HACKERS] [SQL] Case Preservation disregarding case
Chuck McDevitt wrote: At Teradata, we certainly interpreted the spec to allow case-preserving, but case-insensitive, identifiers. Users really liked it that way My 2 thoughts: 1: It seems like this behavior of case sensitive-or-not-identifiers could/should be a config option -- either globally for the server, database, or at the connection/session level. Other databases *do* support this type of granular config of misc SQL behavior -- its essential for shared hosting environments. Without it some users just *cant* make the switch. Quoting all an app's identifiers -- or renaming camel-case to underscored -- show stopper. 2: Even though the spec state different (that identifiers should be treated as case sensitive or else folded), precedence seems to have changed that: a) The databases that enforce this rule are fewer, I believe. IMO SQL is now considered even higher than a 4GL language because it use is so widespread - laymen need to use it. b) the fact that different identifiers of mixed case could even coexist in a table-columns or 'AS' or 'JOIN' -- really represents a more of an err'd design -- and a case-insen option would detect this (unlike the current behavior). It would throw an immediate (fail fast) runtime exception. So I think it's *safer*. (If tbl.rowId and tbl.rowid both exist in a table or AS identifiers, something bad _will_ happen when someone takes over a project) If there were a new default behavior (or just config option added), my vote would, without a doubt, be for case-insens (yet case preserving) mode... even when using quoting identifiers. This case sen. behavior doesn't seem to offer any advantage/safety. ken ---(end of broadcast)--- TIP 6: explain analyze is your friend
