Re: [SQL] Needs Function
On 5/2/07, Penchalaiah P. <[EMAIL PROTECTED]> wrote:
I need a user defined function for the following purpose….
If I pass a string with comma ( , ) separated chars/values…. It should be
appear in next line…
Ex: select get_sep_str ('SK, rp, h, j, 6, 9, kl') from dual;
Output :
SK
rp
h
j
6
9
kl
(Or)
I have one table like this…..
Temp Table:
Deptno number(10)
Empno varchar2(200);
Data in temp table:
Deptno Empno
---
10 B3091,B3092,B3093,B3085
11 3651,6521
12 H3062
Now, I want to display the data like this…..
Deptno Empno
--
10 B3091
10 B3092
10 B3093
10 B3094
11 3651
11 6521
12 H3062
Now, how can I achieve this….
See:
http://archives.postgresql.org/pgsql-general/2005-12/msg00080.php
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] Needs Function
On 5/3/07, Rodrigo De León <[EMAIL PROTECTED]> wrote: See: http://archives.postgresql.org/pgsql-general/2005-12/msg00080.php Man, where was this last week when I needed it. I will have to keep this for future reference. Thanks! -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ==
Re: [SQL] Needs Function
Well, actually it was there last week too :) >>> "Aaron Bono" <[EMAIL PROTECTED]> 2007-05-03 16:14 >>> On 5/3/07, Rodrigo De León <[EMAIL PROTECTED]> wrote: See: http://archives.postgresql.org/pgsql-general/2005-12/msg00080.php Man, where was this last week when I needed it. I will have to keep this for future reference. Thanks! -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com ( http://www.aranya.com/ ) http://codeelixir.com ( http://codeelixir.com/ ) ==
[SQL] Insert into VIEW using RULE. Not possible to use nextval()?
I am having the same problem that is documented elsewhere in the
archives. Namely when you have a INSERT RULE on a VIEW the nextval()
function doesn't behave properly (or it doesn't behave how I'd like it
to).
http://archives.postgresql.org/pgsql-sql/2003-07/msg00333.php
I'm wondering if anything has changed for this in regards to best
practices? Suggested solutions are to change to a trigger or use
currval() for your secondary INSERTS inside the RULE.
A trigger does not apply to my case as I am basically using this as a
shortcut to manually doing two INSERTs. Is there any problems with
using the currval() approach? If I use that within the same call is
there any chance of that not returning the correct value? (e.g. if
this INSERT RULE is being called a 1000 times at once, is it
guaranteed to be correct?
Another option I see is to place the INSERT inside a LOOP. For
example instead of:
INSERT INTO user_activity_single(user_activity_id, activity_date,
user_activity_type_id, user_activity_action_id, user_id, div1)
SELECT nextval('user_activity_user_activity_id_seq'), etc
have:
FOR mviews IN
SELECT nextval('user_activity_user_activity_id_seq') as id,
CURRENT_DATE, 1, 2, 27, 'foo'
LOOP
INSERT INTO user_activity_single(mviews.id, etc...)
END LOOP;
Performance wise this doesn't seem as good. In my case the SELECT
statement would be around 4000 records.
Any tips for me?
Regards,
Collin Peters
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Insert into VIEW using RULE. Not possible to use nextval()?
--- Collin Peters <[EMAIL PROTECTED]> wrote: > I am having the same problem that is documented elsewhere in the > archives. Namely when you have a INSERT RULE on a VIEW the nextval() > function doesn't behave properly (or it doesn't behave how I'd like it > to). > > http://archives.postgresql.org/pgsql-sql/2003-07/msg00333.php > Is your problem that you can't insert more than one record at a time into your INSERTable VIEW? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Insert into VIEW using RULE. Not possible to use nextval()?
The exact problem is that you can't use nextval() with an INSERTable VIEW Problem is the same as that in this post: http://archives.postgresql.org/pgsql-sql/2003-07/msg00333.php On 5/3/07, Richard Broersma Jr <[EMAIL PROTECTED]> wrote: --- Collin Peters <[EMAIL PROTECTED]> wrote: > I am having the same problem that is documented elsewhere in the > archives. Namely when you have a INSERT RULE on a VIEW the nextval() > function doesn't behave properly (or it doesn't behave how I'd like it > to). > > http://archives.postgresql.org/pgsql-sql/2003-07/msg00333.php > Is your problem that you can't insert more than one record at a time into your INSERTable VIEW? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Insert into VIEW using RULE. Not possible to use nextval()?
--- Collin Peters <[EMAIL PROTECTED]> wrote:
> The exact problem is that you can't use nextval() with an INSERTable VIEW
I apoligize if I am not understanding your problem correctly. I am unsure as
to intent behind
using nextval() as opposed to currval().
I do not know if the following DDL will help but I can show you how I preform
inserts across
multiple tables using the rule system:
CREATE RULE Vschematic_insert AS ON INSERT TO Docs.Vschematic
DO INSTEAD (
INSERT INTO Docs.Document ( did, doccode, docnum, docdisc)
VALUES ( DEFAULT, 'schematic', New.docnum, New.docdisc);
INSERT INTO Docs.Drawing ( did, doccode, title1, title2, title3)
VALUES ( Currval('Docs.Document_did_seq'), 'schematic', New.title1,
New.title2, New.title3);
INSERT INTO Docs.Schematic ( did, doccode)
VALUES ( Currval('Docs.Document_did_seq'), 'schematic')
);
For reference the table DDL follows:
CREATE TABLE docs.document (
did serial PRIMARY KEY,
doccode varchar(30) not null,
docnum varchar(30) unique not null,
docdisc textnot null default '',
constraint
document_doccode_chk
check ( doccode in ( 'cpf', 'logicsystem', 'processdetail',
'electricaldetail', 'locationplan', 'logicdiagram',
'loopdiagram', 'schematic', 'wiringdiagram', 'pid',
'isometric', 'airsupplydetail', 'mountingdetail',
'pnuematicdetail', 'functionaldiscription',
'datasheet', 'processmaterialspec',
'loopfoldermiscellaneous', 'loopfolderorficeplate',
'loopfolderinstallation', 'loopfolderswitch',
'loopfolderxmtrctrlind', 'loopfoldercontrolvalve',
'loopfolderanalyzer', 'loopfolderworkscope',
'loopfolderdocumentation')));
CREATE TABLE docs.drawing
(
did integer primary key references
docs.document(did) on delete cascade,
doccode varchar(30) not null,
title1 varchar(50) not null,
title2 varchar(50) not null,
title3 varchar(50) not null,
constraint
drawing_doccode_chk
check ( doccode in ( 'processdetail', 'electricaldetail',
'locationplan',
'logicdiagram', 'loopdiagram', 'schematic',
'pid',
'isometric', 'airsupplydetail',
'mountingdetail',
'pnuematicdetail'))) ;
CREATE TABLE docs.schematic
(
did integer primary key references
docs.drawing(did) on delete cascade,
doccode varchar(30) not null,
cid integer references equ.lcp(cid),
constraint
schematic_doccode_chk
check ( doccode = 'schematic')) ;
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
