[SQL] How to call table returning function with other table
Hi. I have a function that is defined like (which works, sorry for the pseudocode) create type xxx as (id,...); create function calcuate_xxx (integer) returns xxx as $$ select $1, (select sum(amount) from bigtable where something) as a, (select sum(amount) from bigtable where some-other-thing) as b $$ language sql stable; What I cannot figure out is, if I have another query that returns a single column of those id's, how do I get a table back with the function applied to all ids (hence multiple rows). So it is something like "select xxx(id) from othertable where otherwhere = 't'" except that it mangles the columns into an array. I have tried to move the function-call into the fromlist (as it is usually done) but I seem unsuccessful in getting the ID's into it. Thanks in advance Svenne smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] How to call table returning function with other table
2006/3/19, Svenne Krap <[EMAIL PROTECTED]>: > So it is something like "select xxx(id) from othertable where otherwhere > = 't'" except that it mangles the columns into an array. I have tried to > move the function-call into the fromlist (as it is usually done) but I > seem unsuccessful in getting the ID's into it. You have to use something like SELECT (xxx(id)).* FROM othertable WHERE otherwhere = 't', I believe. Markus Bertheau ---(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] How to call table returning function with other table
Perfect. Thanks. Svenne Markus Bertheau wrote: 2006/3/19, Svenne Krap <[EMAIL PROTECTED]>: So it is something like "select xxx(id) from othertable where otherwhere = 't'" except that it mangles the columns into an array. I have tried to move the function-call into the fromlist (as it is usually done) but I seem unsuccessful in getting the ID's into it. You have to use something like SELECT (xxx(id)).* FROM othertable WHERE otherwhere = 't', I believe. Markus Bertheau ---(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 smime.p7s Description: S/MIME Cryptographic Signature
[SQL] Lead and tail quotes with \pset fieldsep
Folks If I want psql to generate CSV files fully-double-quoted I can use pset as follows psql> \pset fieldsep "," However this does not put a quote before the first field and after the last, so each row comes out as 1234","blahblah","sdfgsg","foo","bar Is there a way to use psql to give rows like 1234","blahblah","sdfgsg","foo","bar or is there another tool I should use. COPY doesn't deal with views and restrictions well so it is not an option. Thanks in advance -- David T. Bath [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Lead and tail quotes with \pset fieldsep
Bath, David wrote: > Folks > > If I want psql to generate CSV files fully-double-quoted I can > use pset as follows > psql> \pset fieldsep "," > However this does not put a quote before the first field and > after the last, so each row comes out as > 1234","blahblah","sdfgsg","foo","bar > > Is there a way to use psql to give rows like > 1234","blahblah","sdfgsg","foo","bar > or is there another tool I should use. > > COPY doesn't deal with views and restrictions well so it is > not an option. Just do: CREATE TEMP TABLE x AS SELECT * FROM view; COPY ... x Sorry that is the only way to dump CVS properly. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] PostgreSQL Handling of Special Characters
Hi to all, I am currently using PostgreSQL 8.0.3 My Database uses SQL_ASCII encoding. I have a program in Visual Basic that connects to PostgreSQL using ODBC Connection through File DSN. I insert a text in one fields of my table. For example, I insert a special character to that field. Let's say a character of 150 ASCII code. Which looks like a hypen. When I retrieve the value of that field it gives me a question mark character instead of that 150 ASCII code character. What could be the possible reason of this? Is there a setting in the database configuration to understand that such special characters? Please..I really need an answer to this.. Thanks You. I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] PostgreSQL Handling of Special Characters
2006/3/20, Christian Paul B. Cosinas <[EMAIL PROTECTED]>: > Let's say a character of 150 ASCII code. Which looks like a hypen. > > When I retrieve the value of that field it gives me a question mark > character instead of that 150 ASCII code character. > > What could be the possible reason of this? Perhaps the ODBC driver thinks SQL_ASCII means ASCII and therefore discards all bytes > 127. On PostgreSQL SQL_ASCII really means SQL_ANYTHING, so to speak. Try to use for the database the encoding you really use. Markus Bertheau ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] PostgreSQL Handling of Special Characters
Hi Markus, Can you please elaborate more on this. I'm really lost. Thank You So Much. -Original Message- From: Markus Bertheau [mailto:[EMAIL PROTECTED] Sent: Sunday, March 19, 2006 8:09 PM To: Christian Paul B. Cosinas Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] PostgreSQL Handling of Special Characters 2006/3/20, Christian Paul B. Cosinas <[EMAIL PROTECTED]>: > Let's say a character of 150 ASCII code. Which looks like a hypen. > > When I retrieve the value of that field it gives me a question mark > character instead of that 150 ASCII code character. > > What could be the possible reason of this? Perhaps the ODBC driver thinks SQL_ASCII means ASCII and therefore discards all bytes > 127. On PostgreSQL SQL_ASCII really means SQL_ANYTHING, so to speak. Try to use for the database the encoding you really use. Markus Bertheau I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] PostgreSQL Handling of Special Characters
Christian Paul B. Cosinas wrote: Hi Markus, Can you please elaborate more on this. I'm really lost. Be sure that postgresql ITSELF is handling all chars transparently except ZEROES. Look for the error in the media layer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] PostgreSQL Handling of Special Characters
Christian Paul B. Cosinas wrote: > My Database uses SQL_ASCII encoding. Do yourself a favor and use something else. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] PostgreSQL Handling of Special Characters
My Database uses SQL_ASCII encoding. I just received an email with all accented characters destroyed. UNICODE should be the default for anything in 2006. ---(end of broadcast)--- TIP 6: explain analyze is your friend
