Re: [SQL] OT: array_accum equivalent in Oracle
On 10/12/07, Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote: > Anybody knows if Oracle has an equivalent of PG's array_accum or > ARRAY(subselect) construct? Something like this: CREATE TYPE varchar2_table_t AS TABLE OF VARCHAR2(32767); SELECT job, CAST(MULTISET(SELECT ename FROM emp WHERE job = e.job) AS varchar2_table_t) FROM emp e GROUP BY job; -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] OT: array_accum equivalent in Oracle
On Friday 12 October 2007 17:02:23 Jonah H. Harris wrote: > On 10/12/07, Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote: > > Anybody knows if Oracle has an equivalent of PG's array_accum or > > ARRAY(subselect) construct? > > Something like this: > > CREATE TYPE varchar2_table_t AS TABLE OF VARCHAR2(32767); > SELECT job, CAST(MULTISET(SELECT ename FROM emp WHERE job = e.job) AS > varchar2_table_t) FROM emp e GROUP BY job; Amazing! Works like a charm. I was envisioning some pretty ugly PL/SQL functions to accomplish this. Thanks! -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] OT: array_accum equivalent in Oracle
WARNING: The rest of this post is for somebody who has worked with Oracle and has migrated to PG, or for some other reason has good experience with Oracle. I know this is off-topic for this list and should be asked in an Oracle support-forum, but I don't have access to that, and Uncle Google didn't return any obvious results, so here it goes... Anybody knows if Oracle has an equivalent of PG's array_accum or ARRAY(subselect) construct? I need a way to accomplish this in Oracle: select ARRAY(select t.id from table t where t.user_id = u.id) as id_array, Any hints on where to look in Oracle-docs are welcome. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ ---(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] Accessing field of OLD in trigger
On Fri, 12 Oct 2007 12:00:55 +0200 (CEST) Daniel Drotos <[EMAIL PROTECTED]> wrote: > Hi, > > > I'm working on a row level plpgsql trigger running after delete, > using a 8.0.3 server. It gets a parameter which is a field name of > the OLD record. How can that field be accessed? > > I'd like to do something like: > > for recvar in 'select OLD.'||quote_ident(TG_ARGV[0])... I THINK you are out of luck here. I hear it's possible to do but in one of the other PL languages say pl/tcl, though I can't seem to find an example Josh ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Accessing field of OLD in trigger
Hi, I'm working on a row level plpgsql trigger running after delete, using a 8.0.3 server. It gets a parameter which is a field name of the OLD record. How can that field be accessed? I'd like to do something like: for recvar in 'select OLD.'||quote_ident(TG_ARGV[0])... Daniel Ps: sorry about my previouse try of posting this message, it went to wrong place. ---(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] Accessing field of OLD in trigger
On Oct 12, 2007, at 8:18 AM, Josh Trutwin wrote: On Fri, 12 Oct 2007 12:00:55 +0200 (CEST) Daniel Drotos <[EMAIL PROTECTED]> wrote: Hi, I'm working on a row level plpgsql trigger running after delete, using a 8.0.3 server. It gets a parameter which is a field name of the OLD record. How can that field be accessed? I'd like to do something like: for recvar in 'select OLD.'||quote_ident(TG_ARGV[0])... I THINK you are out of luck here. I hear it's possible to do but in one of the other PL languages say pl/tcl, though I can't seem to find an example Right, "dynamic variables" aren't available in plpgsql. Check out any of the other pl languages available if you can. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] How to escape a memo field as a binary value in postgresql
I want to take the value of my memo field in Paradox and insert/update the value of a binary field in my backend postgresql database. How would I write this sql statement for the sake of this example lets say the field name is pmemo in paradox and the is bymemo a bytea field in Postgresql database.
Re: [SQL] OT: array_accum equivalent in Oracle
On 10/12/07, Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote: > > Something like this: > > > > CREATE TYPE varchar2_table_t AS TABLE OF VARCHAR2(32767); > > SELECT job, CAST(MULTISET(SELECT ename FROM emp WHERE job = e.job) AS > > varchar2_table_t) FROM emp e GROUP BY job; > > Amazing! Works like a charm. > I was envisioning some pretty ugly PL/SQL functions to accomplish this. > > Thanks! No problem. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
