Re: [SQL] PG equivalent to Sybase varbinary
On Monday 06 October 2003 15:40, Bill Pfeiffer wrote: > Anybody know what the Postgresql equivalent to a Sybase varbinary data type > is? I have a package that provides ddl to store a 40 byte/char? varbinary > column in a table and it is failing against postrgresql. Sounds like "bytea" to me - or have you rejected that? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: 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
[SQL] security definer function
Hi I have two functions: A) function defined with "SECURITY DEFINER" B) function defined with "SECURITY INVOKER" Function A calls function B. How is the function b called - with rights of definer of function A, or rather with rights of caller of function A ? Regards, Tomasz Myrta ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Possible to have array as input paramter for a function?
Dear Friends, I am working with Postgres 7.3.4 on RH Linux 7.2 and my windows client is PgAdmin 3. Is it possible to pass a Varchar[] as a input parameter for a PL/pgSQL function. While I tried it give a error Type "varchar[]" does not exists. Does this is supported in Postgres? Anyone have a link or while paper on handling arrays in functions? Please enlighten me on this. Regards Kumar
Re: [SQL] Possible to have array as input paramter for a function?
Hi Kumar, It is possible to pass an array to a PL/pgSQL function, but I believe you must specify the length of the array (at least doing so works for me). E.g. "varchar(20)". Regards, George - Original Message - From: Kumar To: psql Sent: Wednesday, October 08, 2003 5:47 AM Subject: [SQL] Possible to have array as input paramter for a function? Dear Friends, I am working with Postgres 7.3.4 on RH Linux 7.2 and my windows client is PgAdmin 3. Is it possible to pass a Varchar[] as a input parameter for a PL/pgSQL function. While I tried it give a error Type "varchar[]" does not exists. Does this is supported in Postgres? Anyone have a link or while paper on handling arrays in functions? Please enlighten me on this. Regards Kumar
Re: [SQL] Possible to have array as input paramter for a function?
Hi Kumar, Looks like I got up too early this morning - please ignore my previous reply re: varchar(30) (I was looking at the wrong function :-( ). I do use arrays in Pl/pgSQL functions, and have defined them as you did, e.g. varchar[], which does not return an error. What version of PostgreSQL are you using? I am running 7.3.2. Regards, George - Original Message - From: Kumar To: psql Sent: Wednesday, October 08, 2003 5:47 AM Subject: [SQL] Possible to have array as input paramter for a function? Dear Friends, I am working with Postgres 7.3.4 on RH Linux 7.2 and my windows client is PgAdmin 3. Is it possible to pass a Varchar[] as a input parameter for a PL/pgSQL function. While I tried it give a error Type "varchar[]" does not exists. Does this is supported in Postgres? Anyone have a link or while paper on handling arrays in functions? Please enlighten me on this. Regards Kumar
[SQL] UPDATE one table with values from another
I know there is a simple solution, but I can't remember what it is. :( I have two similar tables. I want to update the fields from one table to contain the values form the other. The two tables are: laptop.freshports.org=# \d commit_log_ports Table "public.commit_log_ports" Column | Type | Modifiers ---+--+--- commit_log_id | integer | not null port_id | integer | not null needs_refresh | smallint | not null port_version | text | port_revision | text | Indexes: commit_log_ports_pkey primary key btree (commit_log_id, port_id), needs_refresh btree (needs_refresh) Foreign Key constraints: $1 FOREIGN KEY (commit_log_id) REFERENCES commit_log(id) ON UPDATE CASCADE ON DELETE CASCADE, $2 FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE Triggers: commit_log_ports_insert laptop.freshports.org=# \d commit_log_ports_elements Table "public.commit_log_ports_elements" Column | Type | Modifiers ---+--+--- commit_log_id | integer | not null element_id| integer | not null needs_refresh | smallint | not null port_version | text | port_revision | text | Indexes: commit_log_ports_elements_pkey primary key btree (commit_log_id, element_id) Foreign Key constraints: $1 FOREIGN KEY (commit_log_id) REFERENCES commit_log(id) ON UPDATE CASCADE ON DELETE CASCADE, $2 FOREIGN KEY (element_id) REFERENCES element(id) ON UPDATE CASCADE ON DELETE CASCADE laptop.freshports.org=# I can obtain the values I want with this query: SELECT CLP.* FROM commit_log_ports CLP, ports P, commit_log_ports_elements X WHERE CLP.port_id = P.id AND CLP.commit_log_id = X.commit_log_id AND X.element_id = P.element_id; I started writing the UPDATE and got as far as this before brain fatigue set in: UPDATE commit_log_ports_elements X SET X.needs_refresh = CLP.needs_refresh, X.port_version = CLP.port_version, X.port_revision = CLP.port_revision WHERE X.commit_log_id = commit_log_ports CLP AND X. A clue please? Thank you. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] UPDATE one table with values from another
Dan, > UPDATE commit_log_ports_elements X >SET X.needs_refresh = CLP.needs_refresh, >X.port_version = CLP.port_version, >X.port_revision = CLP.port_revision FROM commit_log_ports CLP WHERE X.commit_log_id = CLP.commit_log_id You can always ask this kind of thing on IRC . -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] UPDATE one table with values from another
On Wed, 8 Oct 2003, Josh Berkus wrote: > > UPDATE commit_log_ports_elements X IIRC, PostgreSQL doesn't like aliases of the update table, so I think you'll need to spell it out in the WHERE. > >SET X.needs_refresh = CLP.needs_refresh, > >X.port_version = CLP.port_version, > >X.port_revision = CLP.port_revision I don't think you need the X.'s here anyway, there's only one update table. ---(end of broadcast)--- TIP 3: 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] UPDATE one table with values from another
On Wed, 8 Oct 2003, Josh Berkus wrote: > Dan, > > > UPDATE commit_log_ports_elements X > >SET X.needs_refresh = CLP.needs_refresh, > >X.port_version = CLP.port_version, > >X.port_revision = CLP.port_revision > > FROM commit_log_ports CLP > WHERE X.commit_log_id = CLP.commit_log_id Thanks Josh. After a 5 hour drive to Hamilton, my brain was only capable of doing the email. > You can always ask this kind of thing on IRC . If I'd been at home, I would have. This laptop of mine is getting pretty old. It took pretty close to 15 minutes for it to set a field to zero in 91,295 rows I need more ram and a faster laptop! cheers ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
