[SQL] How to pass array of values to a stored procedure
Does anyone have any examples of how I would make a stored procedure in plpgsql that would allow for passing a list or arrays of values to be used in an sql IN clause? Like so: select * from table where field1 in (values). Thanks, Curtis
Re: [SQL] How to pass array of values to a stored procedure
Thanks Tom and Tony for the help, I actually implemented the solution using a bpchar[] and hopefully the "field1 = ANY (arrayvalue)" syntax will not hurt the query performance a lot without an index scan. Thanks again, Curtis On 7/18/06, Tony Wasson <[EMAIL PROTECTED]> wrote: > On 7/18/06, Curtis Scheer <[EMAIL PROTECTED]> wrote: > > Does anyone have any examples of how I would make a stored procedure in > > plpgsql that would allow for passing a list or arrays of values to be used > > in an sql IN clause? Like so: select * from table where field1 in (values). > > ---(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] Help with optional parameters
I noticed this one by searching in the archives, as I am working with some “optional” parameters myself and noticed your solution. I just wanted to make one improvement suggestion which is instead of checking whether or not a parameter has been used simply start your query like so Query_base := ‘SELECT * FROM my_table WHERE 1 =1; If you do that then you can just add on any parameters you need or not add any at all. I think that seems to be a bit simpler than having a “has_param” Boolean variable. Thanks, Curtis From: Rob Tester [mailto:[EMAIL PROTECTED] Sent: Thursday, August 17, 2006 8:58 PM To: MaXX Cc: [email protected] Subject: Re: [SQL] Help with optional parameters BEGIN query_base := 'SELECT * FROM my_table '; has_param := FALSE; query_where := ''; IF (a IS NOT NULL) THEN IF (has_param IS FALSE)THEN -- there is no param yet add WHERE to the query query_where := ' WHERE '; ELSE -- there is already something in the WHERE clause, we need to add AND query_where := query_where || ' AND '; END IF; query_where := query_where || 'parama='||a; --beware if param quoting is required has_param := TRUE; -- now there is at least 1 param END IF;
[SQL] Table Relationships
Given the following two tables:
CREATE TABLE public.task
(
taskid int4 NOT NULL DEFAULT
nextval('task_taskid_seq'::regclass),
description varchar,
CONSTRAINT pk_taskid PRIMARY KEY (taskid)
)
public.users
(
userid int4 NOT NULL,
username varchar,
CONSTRAINT pk_userid PRIMARY KEY (userid)
)
I want to record which user “performed the task”
and which user “checked the task”, I’ve come up with a few
ideas on this but I would like to know what the correct way would be to
implement this into my table design.
Thanks,
Curtis
Re: [SQL] Table Relationships
Thanks for the help. From: Aaron Bono [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 31, 2006 2:44 PM To: A. Kretschmer Cc: [email protected] Subject: Re: [SQL] Table Relationships On 10/31/06, A. Kretschmer <[EMAIL PROTECTED]> wrote: am Tue, dem 31.10.2006, um 21:08:24 +0100 mailte A. Kretschmer folgendes: > am Tue, dem 31.10.2006, um 13:32:59 -0600 mailte Aaron Bono folgendes: > > I would go further by adding a type table like this: > > > > operation_type ( > > operation_type_id bigserial (PK), > > You are sure, that you need bigserial? Hey, your idea is okay, but i think, we don't need *BIG*serial for this. Okay? Andreas Sorry, just force of habbit. Serial works or you can just drop the id and use the code as the primary key. You should at the very least put a unique constraint on the code field. -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ==
[SQL] Table relationships
I'm having trouble determining the best way to implement the following
scenario for a customer database.
Given the following tables what is the best way to link an address table to
both the "Master" and the "Detail" tables. Basically there can be many
addresses for each customermaster record and also many address for each
customerdetail record. Will this require two many-to-many tables? Or is
there a better solution I am missing? Is there any easy way to build in a
"Default" address or would that be something to do logically in client code?
Thanks,
Curtis
CREATE TABLE testing.customermaster
(
customermasterid int4 NOT NULL DEFAULT
nextval('testing.customermatser_customermasterid_seq'::regclass),
name varchar NOT NULL,
description varchar,
)
CREATE TABLE testing.customerdetail
(
customerdetailid int4 NOT NULL,
customermasterid int4 NOT NULL,
notes varchar,
closed bool,
customerdepartment varchar(3),
)
CREATE TABLE address
(
addressid int4,
name varchar(40),
addr varchar,
city varchar(42),
st varchar(2),
zip varchar(30)
)
Possible many-to-many tables
CREATE TABLE customermaster_address
(
addressid int4,
customermasterid int4
)
CREATE TABLE customerdetail _address
(
addressid int4,
customerdetailid int4
)
Re: [SQL] Table relationships
->I hope you left out the foreign keys for simplicity. Make sure they get into your database. Yes I left out the foreign keys for simplicity -> Is there a difference between an address for the customer detail and an address for the customer? Not really an address is an address, it's a matter of specify an address for the customer master record which basically represents an entire customer while the customerdetail represents departments within that company that might be at a different address then the company's main office for instance. -> Could you add a specific address to multiple customer and/or customer detail records or is the address only assigned to one? Many addresses can belong to many customer detail records. The customermaster table should only contain one address so that should be a one to many relationship. I guess the real problem is defining which address is the "Main Office" for a given company. So maybe making different "Types" of addresses and referencing them to the customerdetail table is the best way to go? For instance CREATE TABLE customerdetail _address ( addressid int4, customerdetailid int4 addresstypeid varchar ) CREATE TABLE testing.addresstype ( addresstypeid serial NOT NULL, shortdescription varchar(15) NOT NULL, description varchar(100), CONSTRAINT pk_addresstype_shortdescription PRIMARY KEY (shortdescription) ) The only other problem I see is if a particular customer has the same address for all the departments in the company, then I guess the addressed would exist multiple times but in the customerdetail_address table but the user would only have to select that particular record rather then input the same address again. _ From: Aaron Bono [mailto:[EMAIL PROTECTED] Sent: Monday, January 08, 2007 4:43 PM To: [email protected] Subject: Fwd: [SQL] Table relationships -- Forwarded message -- From: Aaron Bono <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > Date: Jan 8, 2007 4:42 PM Subject: Re: [SQL] Table relationships To: Curtis Scheer <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > On 1/8/07, Curtis Scheer <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > wrote: I hope you left out the foreign keys for simplicity. Make sure they get into your database. To answer your questions, I think it prudent to ask a few to get a better understanding of the meaning of your tables: Is there a difference between an address for the customer detail and an address for the customer? Is there some kind of significance to attaching an address to the customer detail as opposed to the customer? Attaching the address to the detail gives it a customer by referencing through the detail. Could you add a specific address to multiple customer and/or customer detail records or is the address only assigned to one? What it gets down to is that you must start with the LOGICAL data model and ask yourself what are the meaning of the relationships and what relationships make sense before you get down to creating the PHYSICAL database. -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com <http://www.aranya.com> http://codeelixir.com <http://codeelixir.com> ==
