[SQL] Converting Query from MS SQL
Dear Friends,
I am working with Postgres 7.3.4 on RH Linux 7.2. While
migrating all the SPs (from MS SQL Server), I come across these lines in MS SQL
Server procedure.
SET @v_sql = "UPDATE "Schema1".employee SET
rec_deleted_flag = 'Y' WHERE empid IN (" + @p_list_ids +
");" EXEC(@v_sql)
Actually to dynamically pass the values for the 'IN' the pass
the @p_list_ids (a comma separated string ) to the variable @v_sql and
then execute it.
How can I change it for postgres?
Thank you very much for ur support
Regards
Kumar
Re: [SQL] output
Popeanga Marian wrote: I am using libpg inside a plugin. For oracle conections i can read the server output with this package dbms_output.read_line (... ). For pgsql is something similar for reading server output ? Tom Lane wrote: Popeanga Marian <[EMAIL PROTECTED]> writes: Now if i don't use psql from where i can read the output ? If you're using libpq directly, you can install a notice processor hook routine to catch NOTICE messages. Otherwise they go to stderr. regards, tom lane Can you tell me more about how to install a notice processor hook routine to catch NOTICE messages ? Thanks, /Marian
Re: [SQL] Converting Query from MS SQL
On Monday 06 October 2003 14:04, Kumar wrote:
> Dear Friends,
>
> I am working with Postgres 7.3.4 on RH Linux 7.2. While migrating all the
> SPs (from MS SQL Server), I come across these lines in MS SQL Server
> procedure.
>
> SET @v_sql = "UPDATE "Schema1".employee SET rec_deleted_flag = 'Y' WHERE
> empid IN (" + @p_list_ids + ");" EXEC(@v_sql)
>
> Actually to dynamically pass the values for the 'IN' the pass the
> @p_list_ids (a comma separated string ) to the variable @v_sql and then
> execute it.
>
> How can I change it for postgres?
Something like (in plpgsql):
EXECUTE ''UPDATE "Schema1".employee SET ...'' || my_list_var || '')'';
See the manuals for more details.
--
Richard Huxton
Archonet Ltd
---(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
[SQL] Multiple table join
Greetings, SQL gurus! I am attempting to select fields Location and Item_Num from table A where A.Location = B.Location, AND select Item_Description from table C, where A.Item_Num = C.Item_Num. Any help would be appreciated. Louise
Re: [SQL] Multiple table join
On Mon, Oct 06, 2003 at 10:26:59 -0600, Louise Cofield <[EMAIL PROTECTED]> wrote: > > I am attempting to select fields Location and Item_Num from table A > where A.Location = B.Location, > > AND > > select Item_Description from table C, where A.Item_Num = C.Item_Num. Just list all three tables in the from item list and include both conditions in the where clause (connected by and). If there is more to your problem than this, you should supply more details. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] suggestion needed for implementation
I need to create triggers on a number of tables that have full text search
capability. The trigger, basically, needs to call select
set_curcfg('default') first to set the locale , then call tsearch2
function on the text columns in the table to update the index. How can I
implement a trigger that does two different thing (select and tsearch2)
together? I was thinking about making them into one function, say,
function updatetbl(), that does two things together. But then, postgresql
does not allow variable length of arguments. updatetbl can not just take
arbitrary number of arguments to pass on tsearch2.
What can I do here to implement this trigger?
Thanks
Wei
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] create new field
Hello I would like to change the type of a column. At the moment, it is varchar(4) but I would like it to be int. All values in the field at the moment are actually integer. I tried a way I had seen in the archives - it foes along the lines of adding a column, using update, drop the old column and rename the new one. alter table tab add column new_col int4; update tab set new_col = "OLD_COL"; ERROR: column "new_col" is of type integer but expression is of type characte r You will need to rewrite or cast the expression OK - so I tried casting. template1=# update tab set new_col = "OLD_COL"::int4; ERROR: Cannot cast type character to integer I understand this - some tables might have characters in the varchar but how to get around it in my case ? I know that my character field has only integers in it ? Thanks Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Multiple table join
That was way too simple -- thank you! -Original Message- From: Bruno Wolff III [mailto:[EMAIL PROTECTED] Sent: Monday, October 06, 2003 10:38 AM To: Louise Cofield Cc: [EMAIL PROTECTED] Subject: Re: [SQL] Multiple table join On Mon, Oct 06, 2003 at 10:26:59 -0600, Louise Cofield <[EMAIL PROTECTED]> wrote: > > I am attempting to select fields Location and Item_Num from table A > where A.Location = B.Location, > > AND > > select Item_Description from table C, where A.Item_Num = C.Item_Num. Just list all three tables in the from item list and include both conditions in the where clause (connected by and). If there is more to your problem than this, you should supply more details. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Multiple table join
On 6 Oct 2003 at 10:26, Louise Cofield wrote: > I am attempting to select fields Location and Item_Num from table A > where A.Location = B.Location, > > AND > > select Item_Description from table C, where A.Item_Num = C.Item_Num. Try: select Location, Item_Num from table A, B, C where A.Location = B.Location and A.Item_Num = C.Item_Num -- Dan Langille : http://www.langille.org/ ---(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] create new field
Chris,
> template1=# update tab set new_col = "OLD_COL"::int4;
> ERROR: Cannot cast type character to integer
>
> I understand this - some tables might have characters in the varchar but
> how to get around it in my case ? I know that my character field has only
> integers in it ?
Actually, you just need to use the to_number function as an intermediary:
UPDATE tab SET new_col = CAST(to_number("OLD_COL", '') AS INT);
--
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] create new field
On Mon, Oct 06, 2003 at 17:35:11 +0100, Chris Faulkner <[EMAIL PROTECTED]> wrote: > > OK - so I tried casting. > > template1=# update tab set new_col = "OLD_COL"::int4; > ERROR: Cannot cast type character to integer > > I understand this - some tables might have characters in the varchar but how > to get around it in my case ? I know that my character field has only > integers in it ? You want to use to_number to do the conversion. ---(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] FK Constraints, indexes and performance
--- Tom Lane <[EMAIL PROTECTED]> wrote: > It looks to me like the 7.3 planner will not choose indexscans for the FK > check queries in this example, because the comparison operators are > misconstrued as shown in this thread: > http://archives.postgresql.org/pgsql-hackers/2003-03/msg00997.php > The equality operator on your domain is taken to be "oideq" which won't > be the same operator associated with the index on the column. > > This seems to be fixed in 7.4. In 7.3 I'd counsel not introducing > domains unnecessarily. > > regards, tom lane It looks like it worked. I moved to 7.4.b4 and, in my case, performance improvement on insert is drastic (about 30x). Thanks __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
