[SQL] select into
Hi guys, Am new to sql scripting so this might be a stupid question. Am getting an error while trying to do the following SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id; where svc_data_rec is defined as record and svc_tbl_name is a varchar that holds the name of a table and sub_id is another varchar. the error message is ERROR: syntax error at or near "$1" at character 17 QUERY: SELECT * from $1 where 'uid' = $2 CONTEXT: PL/pgSQL function "foo" line 132 at select into variables LINE 1: SELECT * from $1 where 'uid' = $2 where am i going wrong? it seems that the variables are not being substituted with their values correctly. Thank you, - Sponsored Link Mortgage rates near 39yr lows. $420,000 Mortgage for $1,399/mo - Calculate new house payment
Re: [SQL] select into
am Wed, dem 22.11.2006, um 0:28:15 -0800 mailte Mulham freshcode folgendes: > Hi guys, > >Am new to sql scripting so this might be a stupid question. Am getting an > error while trying to do the following > > SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id; > > where svc_data_rec is defined as record and svc_tbl_name is a varchar that > holds the name of a table and sub_id is another varchar. the error message is You should rewrite your plpgsql-function. You can't handle with string-vars in this way, you must create a string with your complete sql and EXECUTE this string. Read http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 1: 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] select into
> Hi guys, > >Am new to sql scripting so this might be a stupid question. Am getting an > error while trying > to do the following > > SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id; > > where svc_data_rec is defined as record and svc_tbl_name is a varchar that > holds the name of a > table and sub_id is another varchar. the error message is > >ERROR: syntax error at or near "$1" at character 17 >QUERY: SELECT * from $1 where 'uid' = $2 >CONTEXT: PL/pgSQL function "foo" line 132 at select into variables >LINE 1: SELECT * from $1 where 'uid' = $2 > >where am i going wrong? it seems that the variables are not being > substituted with their > values correctly. Notice: http://www.postgresql.org/docs/8.2/interactive/sql-selectinto.html and particularly this example: http://www.postgresql.org/docs/8.2/interactive/sql-selectinto.html#AEN54014 Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] select into
Richard Broersma Jr <[EMAIL PROTECTED]> schrieb: > > Hi guys, > > > >Am new to sql scripting so this might be a stupid question. Am getting > > an error while trying > > to do the following > > > > SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id; > > > > where svc_data_rec is defined as record and svc_tbl_name is a varchar that > > holds the name of a > > table and sub_id is another varchar. the error message is > > > >ERROR: syntax error at or near "$1" at character 17 > >QUERY: SELECT * from $1 where 'uid' = $2 > >CONTEXT: PL/pgSQL function "foo" line 132 at select into variables > >LINE 1: SELECT * from $1 where 'uid' = $2 > > > >where am i going wrong? it seems that the variables are not being > > substituted with their > > values correctly. > > Notice: > http://www.postgresql.org/docs/8.2/interactive/sql-selectinto.html > and particularly this example: > http://www.postgresql.org/docs/8.2/interactive/sql-selectinto.html#AEN54014 > Regards, Richard, the plain SQL "INSERT INTO " is an other thing as the original problem... He is in a PL/pgsql - function and he should rewrite this to use EXECUTE ... INTO Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(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] select into
Mulham freshcode <[EMAIL PROTECTED]> writes: >Am new to sql scripting so this might be a stupid question. Am getting an > error while trying to do the following > SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id; The error message shows that this is getting rewritten into >QUERY: SELECT * from $1 where 'uid' = $2 so the problem is that you are using svc_tbl_name as a plpgsql variable, and plpgsql is not bright enough to realize that it shouldn't substitute the variable value at this particular spot in the query. You need to change the variable name to something that won't conflict. In general, don't use plpgsql variables that are named the same as any SQL tables or columns you need to mention in the function. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] select into
--- Andreas Kretschmer <[EMAIL PROTECTED]> wrote: > Richard, the plain SQL "INSERT INTO " is an other thing as the > original problem... > > He is in a PL/pgsql - function and he should rewrite this to use EXECUTE > ... INTO > I see, I am not even a novice yet when it comes PL/pgsql. Thanks for the clarification. Regards, Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Regular Expressions, LIKE, and indexes
From everything I've been able to find, it seems that the only way to get front-anchored regular expressions or LIKE patterns beginning with constants to use an index is to have previously initialized your database using the C locale. Is this still true? I'm trying to do something like: SELECT keyword FROM keywords WHERE keyword ~ '^foo'; or SELECT keyword FROM keywords WHERE keyword like 'foo%'; Are there any other functions that can provide equivalent results while using an index, or am going to have drop UTF-8 and recreate my database? Thanks, Travis
Re: [SQL] Regular Expressions, LIKE, and indexes
Travis Whitton wrote: SELECT keyword FROM keywords WHERE keyword like 'foo%'; Are there any other functions that can provide equivalent results while using an index, or am going to have drop UTF-8 and recreate my database? UTF-8 isn't your locale, it's your character set (or encoding). Your locale is something like en_GB.UTF-8 (for British sorting). You need to define your indexes in such a way that the LIKE can figure things out. That means using a special operator-class (so that you redefine how less-than and greater-than are defined). It takes longer to explain than to do, so I'll direct you to the relevant "CREATE INDEX" example in the manuals: http://www.postgresql.org/docs/8.1/static/indexes-opclass.html -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] How to check coding
Hi I have a table in SQL_ASCII. Clients with different language versions of Windows can write data in tha table using different coding systems (polish, english, cyrillic). Next i would like to convert each line of given column to UTF8, but each row can use a different coding windows-125*. Who know how can I check coding in this rows with diffrect coding? I tried use iconv and sql command convert, but these commands dont returns any errors and they can convert rows (ex. in windows-1251) from windows-1252 to utf8 whithout any errors, but it is incorrect. For example (in this row data is in windows-1251): SELECT CONVERT((select "2" from langset where id_langset=1),'windows- 1250','UTF8') I got: ĺäńôôô SELECT CONVERT((select "2" from langset where id_langset=1),'windows- 1251','UTF8') I got: едсффф ICONV: iconv -c -f WINDOWS-1250 -t UTF8 afile ôűâŕďń÷˙ěçĺęó iconv -c -f WINDOWS-1251 -t UTF8 a фывапсчямзе ку ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
