[SQL] How to declare cursor if tablename is a variable?
See this: “CREATE or replace function geo_polygon(tablename varchar) RETURNS integer AS $$ DECLARE objectid varchar; tab varchar; x1 float; y1 float; obj_num integer; point_num integer; polygonstr varchar; cur_obj CURSOR FOR SELECT mrc_xy_position FROM tablename; cur_point CURSOR FOR SELECT x,y FROM tablename where mrc_xy_position = objectid; BEGIN …….“ Error if call the function. Neither to use “execute” . Who can solve it? Thanks! _ 享用世界上最大的电子邮件系统― MSN Hotmail。 http://www.hotmail.com ---(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
[SQL] reindex database
Hi, It would be so nice to use REINDEX DATABASE command without name, for the current database. Now the command is useless when a database schema script refers just itself, and the database name is not establish. I have to use REINDEX TABLE name instead searching all the database tables. What is your opinion ? Regards, Sabin ---(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] reindex database
Στις Τετάρτη 14 Μάρτιος 2007 14:44, ο/η Sabin Coanda έγραψε: > Hi, > > It would be so nice to use REINDEX DATABASE command without name, for the > current database. > Now the command is useless when a database schema script refers just > itself, and the database name is not establish. I have to use REINDEX TABLE > name instead searching all the database tables. > > What is your opinion ? /contrib/reindexdb/reindexdb -a reindexes everything. Also your script would do a #!/bin/csh set mydb=`psql -q -t -c "SELECT current_database()" | tr -d " \n"` /contrib/reindexdb/reindexdb -d $mydb > > Regards, > Sabin > > > > ---(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 -- Achilleas Mantzios ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How to declare cursor if tablename is a variable?
On Wed, Mar 14, 2007 at 08:14:07AM +, hu js wrote: > CREATE or replace function geo_polygon(tablename varchar) RETURNS > [...] >cur_obj CURSOR FOR SELECT mrc_xy_position FROM tablename; >cur_point CURSOR FOR SELECT x,y FROM tablename where mrc_xy_position = > objectid; > > Error if call the function. Neither to use “execute” . Who can solve > it? Thanks! Are you sure you need to use cursors? Can you not build the query strings and use EXECUTE or "FOR variable IN EXECUTE query LOOP"? -- Michael Fuhr ---(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] import CSV file
Rodrigo De León wrote: On 3/13/07, T E Schmitz <[EMAIL PROTECTED]> wrote: This is pretty ingenious! Just a few minor problems: - how does COPY know which column is which? - how do I specify DELIMITER as TAB? See: http://www.postgresql.org/docs/8.2/static/sql-copy.html Thank you for the links. I had read of the manual entry for the COPY command and I know that I need something like psql -h lolek -U tes -d stockmarket -c "copy history from STDIN DELIMITER AS TAB CSV" < /tmp/FTSE.csv My problem is how to specify the tab character on the command line seeing as the COPY command is already enclosed in quotes. When creating CSV output I use -F $'\t' on Unix. However, DELIMITER AS $'\t' doesn't work. Also, it's nopt happy about the date format : 2007/02/09 See: http://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-DATESTYLE -- Regards, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] import CSV file
T E Schmitz wrote: Rodrigo De León wrote: On 3/13/07, T E Schmitz <[EMAIL PROTECTED]> wrote: This is pretty ingenious! Just a few minor problems: - how does COPY know which column is which? - how do I specify DELIMITER as TAB? See: http://www.postgresql.org/docs/8.2/static/sql-copy.html Thank you for the links. I had read of the manual entry for the COPY command and I know that I need something like psql -h lolek -U tes -d stockmarket -c "copy history from STDIN DELIMITER AS TAB CSV" < /tmp/FTSE.csv My problem is how to specify the tab character on the command line "copy history from STDIN DELIMITER AS '\t' CSV" is the answer Also, it's nopt happy about the date format : 2007/02/09 The correct delimiter solved my date problem, too. -- Regards, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Joins on many-to-many relations.
Hi, Doing a join on one-to-many relations (like "orders" joining "custumors") is easy, but what if there are many-to-many relations involved? Consider this scenario of three (simplified) tables: people - id - name accounts - id - owner_id REFERENCES people account_co_owners - co_owner_id REFERENCES people - account_id REFERENCES accounts I need a query that allows the user to search for accounts by giving names of either co-owners or owners. Currently, the query responsible is this: SELECT DISTINCT ON (account.id) account.* FROM accounts AS account INNER JOIN people AS owner ON owner.id = account.owner_id OR owner.id IN (SELECT co_owner_id FROM account_co_owners WHERE account_id = account.id AND co_owner_id = owner.id) WHERE owner.name LIKE '%user supplied search string%'; But this query is too slow for my taste. It takes about 3 seconds, for only 800 accounts). Without the subselect in the JOIN statement (and therefor without the ability to search based on the co-owner names), it is significantly faster. My question is, can joining many-to-many relations be done in a better way than what I'm doing here? Thanks in advance. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] ordering by multiple columns
Hi! I'm trying to order a list in which the first parameter to order is a specific field, and the second should vary depending on a specific condition. More explicit, I would like to do something like: - ORDER BY a.column1, [b.column2 if c.value != 19 || c.column2 if c.value==19] That is to say, use a column if a value on each row isn't 19, OR use a different column if it isn't. How could I solve this? I've got to have a global order over my query, so I cannot separate it into two queries, etc :-? Thank you!
Re: [SQL] import CSV file
On Tue, Mar 13, 2007 at 11:52:17PM +, T E Schmitz wrote: > Also, it's nopt happy about the date format : 2007/02/09 You may need to fiddle with your date style. It works for me on 8.1: SELECT '2007/02/09'::date; date 2007-02-09 (1 row) A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(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] ordering by multiple columns
Pablo Barrón <[EMAIL PROTECTED]> schrieb: > > Hi! > > I'm trying to order a list in which the first parameter to order is a > specific > field, and the second should vary depending on a specific condition. More > explicit, I would like to do something like: > > - ORDER BY a.column1, [b.column2 if c.value != 19 || c.column2 if > c.value==19] > > That is to say, use a column if a value on each row isn't 19, OR use a > different column if it isn't. How could I solve this? I've got to have a > global You can do something like this: order by a.column1, case when c.value != 19 then b.column2 else c.column2 end; *untested*, but the way is to use a case when then ... else ... end; - construct. 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] ordering by multiple columns
On 3/14/07, Pablo Barrón <[EMAIL PROTECTED]> wrote: Hi! I'm trying to order a list in which the first parameter to order is a specific field, and the second should vary depending on a specific condition. More explicit, I would like to do something like: - ORDER BY a.column1, [b.column2 if c.value != 19 || c.column2 if c.value==19] That is to say, use a column if a value on each row isn't 19, OR use a different column if it isn't. How could I solve this? I've got to have a global order over my query, so I cannot separate it into two queries, etc :-? Thank you! SELECT (...) ORDER BY a.column1, CASE WHEN c.VALUE <> 19 THEN b.column2 ELSE c.column2 END; ---(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] Joins on many-to-many relations.
At 11:39 AM 3/14/07, Wiebe Cazemier wrote: Consider this scenario of three (simplified) tables: people - id - name accounts - id - owner_id REFERENCES people account_co_owners - co_owner_id REFERENCES people - account_id REFERENCES accounts I need a query that allows the user to search for accounts by giving names of either co-owners or owners. Currently, the query responsible is this: SELECT DISTINCT ON (account.id) account.* FROM accounts AS account INNER JOIN people AS owner ON owner.id = account.owner_id OR owner.id IN (SELECT co_owner_id FROM account_co_owners WHERE account_id = account.id AND co_owner_id = owner.id) WHERE owner.name LIKE '%user supplied search string%'; But this query is too slow for my taste. A performance question should always include the output of EXPLAIN ANALYZE. I think the problem is database design. If you added a boolean column into accounts table which would indicate owner/co-owner; then all data from account_co_owner could be merged into accounts and the query would be much simpler to code. I don't expect this code to be any quicker; but I think it more clearly identifies the problem with your design: SELECT accounts.* from accounts inner join ( SELECT account.* FROM ( select id,owner_id from accounts union select account_id,co_owner_id from account_co_owners ) as account INNER JOIN ( SELECT id FROM people WHERE name LIKE '%user%' ) AS owner on account.owner_id = owner.id ) as acct on acct.id=accounts.id; ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL]unsubscribe
unsubscribe ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL]
Unsubscribe Nigel Derek Henry [EMAIL PROTECTED] eBusiness Architect
Re: [SQL] Joins on many-to-many relations.
On Wednesday 14 March 2007 18:58, Frank Bax wrote:
> A performance question should always include the output of EXPLAIN ANALYZE.
>
> I think the problem is database design. If you added a boolean column into
> accounts table which would indicate owner/co-owner; then all data from
> account_co_owner could be merged into accounts and the query would be much
> simpler to code.
>
> I don't expect this code to be any quicker; but I think it more clearly
> identifies the problem with your design:
>
> SELECT accounts.* from accounts
> inner join
>( SELECT account.* FROM
> ( select id,owner_id from accounts
>union
>select account_id,co_owner_id from account_co_owners
> ) as account
> INNER JOIN
> ( SELECT id FROM people WHERE name LIKE '%user%' ) AS owner
> on account.owner_id = owner.id
>) as acct on acct.id=accounts.id;
I can't say I really understand that query, but a union is not going to
work, because account_co_owners is nothing more than a join-table,
whereas accounts contains all the information belonging to an account.
An account has one primary owner, indicated by the owner_id, and one or
more co-owners, described by the account_co_owners table. Owners and
co-owners are all of type people. I don't see anything wrong with this
design.
In the real word, an account is actually a transaction_account. This
is the real query ('%KOE%' is the user supplied search string):
SELECT DISTINCT ON (account.id) account.*
FROM trade.transaction_accounts AS account
INNER JOIN people.people AS owner
ON owner.id = account.owner_id
OR owner.id IN (SELECT co_owner_id
FROM trade.transaction_account_co_owners
WHERE account_id = account.id
AND co_owner_id = owner.id)
WHERE upper(account.description) LIKE '%KOE%'
OR upper(owner.name) LIKE '%KOE%'
OR upper(owner.familiar_name) LIKE '%KOE%'
OR upper(owner.full_name) LIKE '%KOE%'
I discovered that removing the subselect (the entire second condition of
the join actually) is not the only thing that speeds it up. If I remove
the LIKE check on account.description, it's also a lot faster (152 ms
as opposed to 2915 ms), although not as fast as without the subselect.
I don't understand why that makes such a big difference. There is an
index on upper() on the field.
This is the EXPLAIN ANALYZE output:
Unique (cost=0.00..1061826.94 rows=800 width=551) (actual
time=430.172..6492.619 rows=4 loops=1)
-> Nested Loop (cost=0.00..1061644.80 rows=72856 width=551) (actual
time=430.165..6492.585 rows=5 loops=1)
Join Filter: (((upper(("outer".description)::text) ~~ '%KOE%'::text)
OR (upper(("inner".name)::text) ~~ '%KOE%'::text) OR
(upper(("inner".familiar_name)::text) ~~ '%KOE%'::text) OR
(upper(("inner".full_name)::text) ~~ '%KOE%'::text)) AND (("inner".id =
"outer".owner_id) OR (subplan)))
-> Index Scan using transaction_accounts_pkey on transaction_accounts
account (cost=0.00..36.80 rows=800 width=551) (actual time=0.014..3.717
rows=800 loops=1)
-> Seq Scan on people "owner" (cost=0.00..54.08 rows=1208
width=1552) (actual time=0.002..2.541 rows=1208 loops=800)
SubPlan
-> Seq Scan on transaction_account_co_owners (cost=0.00..2.04
rows=1 width=4) (actual time=0.029..0.029 rows=0 loops=4796)
Filter: ((account_id = $0) AND (co_owner_id = $1))
Total runtime: 6492.709 ms
But, I can't really be asking you to fully analyze my query, unless you see
something obvious that can be improved. My question was mainly general;
if there is a better way than using subselects to join two tables which
are only connected to eachother through a join-table (containing only
references to the two tables in question). Subselects are usually very
slow, aren't they?
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL] unsubscribe
unsubscribe _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
