[SQL] Update
OK, I figured it out. It wasn't the sum(int4) thing... I have a query that looks like this: select pnum from part where func1(pnum) and func2(pnum); Func1 takes less time to execute than func2. I was using func1 to "narrow the field" of records so the query would not take so long to execute. After upgrading to 7.1 the query got real slow. After changing the query to: select pnum from part where func2(pnum) and func1(pnum); The query went back to its normal time. It appears that the first function would get evaluated first under 7.0.3 but the last function gets evaluated first under 7.1. Is that accurate? Is there a way to control which functions are given precidence? Kyle begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] 7.1 grant/revoke speed
Any reason why I should expect grants and/or revokes to be slower under 7.1RC3? I have a script that grants all our privileges and it takes about 5 to 10 times longer to run than it did under 7.0.3. begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard ---(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] Need help with EXECUTE function
Folks (esp Jan, Tom & Michael):
I have a search function I'm testing, which uses the EXECUTE function to
perform a dynamic set of string comparisons according to whcih criteria
the user passes along. Unfortunately, this requires me to triple-nest
my quotes and I can't seem to get it right. No matter how I play
with the function, it keeps blowing up due to "unterminated strings".
This happens even if I terminate the function short of the EXECUTE
statement.
Can someone *please* give me some pointers?
Function text:
create function fn_search_orders ( INT4, VARCHAR, INT2, VARCHAR, DATE,
VARCHAR,
INT4, INT4, VARCHAR, VARCHAR, INT4 )
RETURNS int4 AS '
DECLARE
v_client ALIAS for $1;
v_clname ALIAS for $2;
v_status ALIAS for $3;
v_datesearch ALIAS for $4;
v_start ALIAS for $5;
v_address ALIAS for $6;
v_contact ALIAS for $7;
v_staff_usq ALIAS for $8;
v_staff_name ALIAS for $9;
v_temps ALIAS for $10;
v_temp_usq ALIAS for $11;
search_id INT4;
query_string VARCHAR;
where_string VARCHAR;
search_count INT4;
BEGIN
search_id := NEXTVAL(''search_sq'');
query_string := ''INSERT INTO searches ( search_sq, usq ) SELECT '' ||
CAST(search_id AS VARCHAR)
|| '', usq FROM sv_orders WHERE '';
where_string := ;
IF v_client > 0 THEN
where_string := '' AND client_usq = '' || CAST(v_client AS varchar);
END IF;
IF trim(v_clname) <> THEN
where_string := where_string || '' AND client_name ~* ||
v_clname ||
'';
END IF;
IF v_status <> 0 THEN
where_string := where_string || '' AND status = '' || CAST(v_status AS
VARCHAR);
ELSE
where_string := where_string || '' AND status > 0'';
END IF;
IF v_start > ''1950-01-01''::DATE THEN
IF v_datesearch = ''BEFORE'' THEN
where_string := where_string || '' AND start_date <
||
to_char(v_start, ''-MM-DD'') || '';
ELSE
where_string := where_string || '' AND start_date >
||
to_char(v_start,
''-MM-DD'') || '';
END IF;
END IF;
IF trim(v_address) <> THEN
where_string := where_string || '' AND order_address ~* ||
v_address
|| '';
END IF;
IF v_staff_usq > 0 THEN
where_string := where_string || '' AND resp_staff_usq = '' ||
CAST(v_staff_usq AS VARCHAR);
END IF;
IF trim(v_staff) <> THEN
where_string := where_string || '' AND staff_name ~* ||
v_staff ||
'';
END IF;
IF trim(v_contact) <> THEN
where_string := where_string || '' AND order_contact ~* ||
v_contact
|| '';
END IF;
IF trim(v_temps) <> THEN
where_string := where_string || '' AND list_temps ~* ||
v_temps ||
'';
END IF;
IF v_temp_usq > 0 THEN
where_string := where_string || '' AND usq IN(SELECT order_usq FROM
assignments WHERE candidate_usq = '' || CAST(v_temp_usq AS VARCHAR) ||
'')'';
END IF;
where_string := substr(where_string, 5);
EXECUTE query_string || where_string;
SELECT count(*) INTO search_count
FROM searches WHERE search_sq = search_id;
IF search_count > 0 THEN
RETURN search_id;
ELSE
RETURN 0;
END IF;
END;'
LANGUAGE 'plpgsql';
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-profit organizations. San Francisco
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Casting numeric to text
Is there any possibility to cast numeric to text in Postgres 7.0.3? shop=# select cast(price as text) from products; ERROR: Cannot cast type 'numeric' to 'text' Hans ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Sql error
Respected Sir/Madam, I visit your site find a lot of information. I download postgres7.0.3 latest version and install it on Redhat6.1 server. I follow all the steps in your Install file. I create a database by createdb testdb command. After that I connect it by 'psql testdb' command. It connect sucessfully. but after connection when I type testdb=>\df it gives this error: " ERROR: Function 'oid8types(oidvector)' does not exist. Unable to identify a function that stasfies the given argument type You may need to add explocit typecast. " Pl tell me how to solve this problem. I want to see the function available with the database. Regards virender singh mumbai-india.
[SQL] Aliasing on tables ...
Is there any possibility to access a column using the alias for the column in the where clause? Queries like that don't seem to work: SELECT name || ' bought ' || amount || ' units' AS result FROM sales where result='abc'; ERROR: Attribute 'result' not found That works perfectly well: SELECT name || ' bought ' || amount || ' units' AS result FROM sales where name || ' bought ' || amount || ' units'='abc'; result (0 rows) Is there any possibility to do this with PostgreSQL 7.0.3? Hans ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] pg_dump and oid
Hello, I am using postgreSQL 7.02 on RH Linux 6.2. I have noticed that dumping out a table containing oid (BLOBs) makes trouble. How can I managed this in order to have my BLOBs backed up correctly ? If I don't take care, I have a table with oid that don't point on any object id (relation does not exist). Thanks -- Richard NAGY Presenceweb ---(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] Re: Index on View ?
The Brand-X DBMS have 'indexed views' but in all their explanations I can't see where they would be useful. SQL Server 2000 creates a 'clustered index' on the view, then lets you create other unclustered indexes in addition to it. Any time one of the source tables is updated, the clustered index needs to be updated, which to me means instantiating the view, which means tons of overhead. They talk about it being handy if there are aggregates in the view, but why not create a table to hold the aggregated data and updated with a trigger/rule? Richard Huxton wrote: > From: "Keith Gray" <[EMAIL PROTECTED]> > > > Is it possible (feasible) to create an index on a view. > > > > We have a large table and a defined sub-set (view) > > from this table, would it be possible to keep an index > > of the sub-set. > > I don't think so - the view is basically just a select rule that rewrites > queries based on it. > > Indexes on underlying tables should be used though. Difficult to suggest > what indices you might need without knowing the view/tables/queries > involved. > > - Richard Huxton > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: UNION in a VIEW?
In article <[EMAIL PROTECTED]>, "Josh Berkus" <[EMAIL PROTECTED]> wrote: > Tom, Gordon, > Hmmm ... I'm pretty used an external WHERE clause being applied to the > output of the view, rather than pushed down into the member selects of > the UNION, in the same way as if the UNION query were a subselect. > Coming from a SQL Server background, I'd actually find the suggested > behavior rather confusing (as well as tough for you guys to implement). Like I said, I'm not a backend guru. However, SQL Server and DB2 both *appear* to be pushing down the WHERE clause. They may not be, but they both process the query nearly instantaneously on large tables, which leads me to speculate that they do. PostgreSQL goes off and munches for a *long* time on the same view/query, whereas if I write a query which explicitly distributes the WHERE then PostgreSQL processes the query very fast (faster than DB2 or SQL Server). So, I can only guess what's happening "behind the curtain", but this is what I'm observing. Plus, I think that if the WHERE clause were applied to the results of the VIEW, that would require storing those results in temp space, and for large tables would be very slow. I believe that's the whole reason that VIEWs' underlying queries can be merged/rewritten with the "calling" query? Thanks, Gordon. -- It doesn't get any easier, you just go faster. -- Greg LeMond ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] help
I read your document about the porting from Oracle to PgSql and I would like to know if you can say to me if there is an equivalent on an the option NOWAIT for a request SELECT ... FOR UPDATE (for Oracle) under PgSql. This option don't wait a previus unlock and return the information like the line can't be lock. Oracle: SELECT * FROM toto WHERE id =4 and value = 'hello' FOR UPDATE NOWAIT; PsSql: Tanks a lot ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Can anyone explain how this works?
Hi, I recently posted this same question a few weeks back but lost the reply someone kindly sent. The question again how exactly does this query work: it will return all attributes and respective data types of a given table': select attname, typname from pg_class c, pg_attribute a, pg_type t where relname = relation_name and attrelid = c.oid and atttypid = t.oid and attnum > 0 order by attnum; Many thanks, Rob Burne.
Re: [SQL] Trigger Function and Html Output
On Sun, Apr 01, 2001 at 01:21:43PM -0400, Stef Telford wrote: > maybe i am naive in thinking this way, but surely the a database function > formatting the returned string must be quicker then perl. (speaking > generically of course, i conceed that there are times when the reverse > is true) Formatting data is not always so simple. You definately need to encode entities and such, and doing this from SQL could get to be a pain. Particularly when you decide that now you want this column to contain a link to some other page, or that rows should alternate colors, or whatever. Doing some of the work in the database is generally a good thing, but I think in this case it's just a hell of a lot easier in Perl because it's a more powerful language. -- Christopher Masto Senior Network Monkey NetMonger Communications [EMAIL PROTECTED][EMAIL PROTECTED]http://www.netmonger.net Free yourself, free your machine, free the daemon -- http://www.freebsd.org/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
