Re: [SQL] How to hand over array as variable in plpgsql function?
Hi Tom (and hello all again), thanks for your reply. > Seems like using EXECUTE is the hardest possible way to do this. Why > don't you just SELECT? > > SELECT id FROM precip_arrays WHERE b = precip_control INTO id_result; How should the code look like to do this? I am using EXECUTE because I don't know how to write the SQL equivalent. How would the SQL code look like if I have these two statements (one returning an array, the other taking this array and compares it to some other arrays in the db): SELECT precip_control FROM precip_arrays WHERE id = 400; --returns an array SELECT id FROM precip_arrays WHERE [THE RETURNED ARRAY] = precip_scenario;--compares the above array to the arrays in colum "precip_scenario". This obviously does NOT work: SELECT id FROM precip_arrays WHERE (SELECT precip_control FROM precip_arrays WHERE id = 400;) = precip_control; Is something like the above statement possible? Sorry for my ignorance and thanks in advance again Jan -- Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Performing intersection without intersect operator
Hi all, I want to perform an intersection between several select queries but without using the INTERSECT keyword. select userid from orders where productid=1 INTERSECT select userid from orders where productid=2 I want to transform it without the INTERSECT. Thanks to all Nacef
Re: [SQL] Performing intersection without intersect operator
Howdy, Nacef, Try this, SELECT a.userid FROM orders a JOIN orders b USING (userid) WHERE a.productid = 1 AND b.productid = 2 Best, Oliveiros - Original Message - From: Nacef LABIDI To: pgsql-sql@postgresql.org Sent: Tuesday, October 28, 2008 11:14 AM Subject: [SQL] Performing intersection without intersect operator Hi all, I want to perform an intersection between several select queries but without using the INTERSECT keyword. select userid from orders where productid=1 INTERSECT select userid from orders where productid=2 I want to transform it without the INTERSECT. Thanks to all Nacef
Re: [SQL] Performing intersection without intersect operator
Nacef LABIDI wrote: I want to perform an intersection between several select queries but without using the INTERSECT keyword. select userid from orders where productid=1 INTERSECT select userid from orders where productid=2 I want to transform it without the INTERSECT. (select userid from orders where productid=1 UNION select userid from orders where productid=2) EXCEPT (select userid from orders where productid=1 EXCEPT select userid from orders where productid=2) EXCEPT (select userid from orders where productid=2 UNION select userid from orders where productid=1) -- 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 hand over array as variable in plpgsql function?
"Jan Peters" <[EMAIL PROTECTED]> writes: >> Seems like using EXECUTE is the hardest possible way to do this. Why >> don't you just SELECT? >> >> SELECT id FROM precip_arrays WHERE b = precip_control INTO id_result; > How should the code look like to do this? Uh ... just like that. > SELECT precip_control FROM precip_arrays WHERE id = 400; --returns an array > SELECT id FROM precip_arrays WHERE [THE RETURNED ARRAY] = > precip_scenario;--compares the above array to the arrays in colum > "precip_scenario". Sure, SELECT precip_control INTO some_local_array_variable FROM ... and then use the variable in the next command. > This obviously does NOT work: > SELECT id FROM precip_arrays WHERE (SELECT precip_control FROM precip_arrays > WHERE id = 400;) = precip_control; Well, it would if you got rid of the first semicolon, although most people would prefer to write this as a join. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] join table?
Hello, list. First post here, I think. So I've worked with several *lousy* DB engines and *poorly* designed databases behind crappy applications over these many years. I look forward to moving everything to PG. First issue is addressed. Now with the grand opportunity to be hoist upon my own petard on issues 2 and 3, DB design-wise, I have a question. Or several, but I'll try to stick to one. ;-) I have a situation where a location may belong to a larger corporation, for instance. In some cases, they may belong to more than one corporation. Corporation is someone we-wee can bill or pay. Everyone else is not belonging to this set. There's probably a name for this kind of model and it's probably trivial but I'm always in the dark when it comes to CS terms. Anyway, my thought is to make a table with two columns (loc_id, corp_id) and unique ID would be the combination of the two. Is that about right? I guess the question is answered by knowing how I need to use the information..?? Well, to find possible 'billable' locations, i.e. those with a 'corp' ID (this ID relates to a certain structure of charges, for example). The corp table will have a default location for AR/AP, which could be the same ID as the location searched for. Presenting a corp should give a list of possibly multiple locations. And so on… So, pretty much I would use a join with this table depending on how I want to use the data..?? I'm trying to be flexible but combine four different tables of addresses, keeping the functions they represent. Or, like that. Anyway, I'm just asking generally if I could be painting myself into a corner here. And hoping I'll eventually find a way to ask the right questions. TIA for any help or suggestions. '-) Cheers, -- |\ /|| | ~ ~ | \/ ||---| `|` ? ||ichael | |iggins\^ / michael.higgins[at]evolone[dot]org -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
