[SQL] Re: Maybe a Bug, maybe bad SQL
On Wed, Mar 21, 2001 at 10:49:41AM -0500, Bruce Momjian wrote: > > Note also that it's a mailing list cultural thing: many lists operate > > in a 'post only to the list' mode. Those of us on the pgsql lists do the > > 'list and person' thing, in response to direct questions, for the reasons > > Bruce and D'Arcy point out. Note that by knowing the reasons, one may > > then make informed decisions, like my posting of this message directly > > to the list only, since it's a peripheral issue and multiple people > > are involved in the conversation. It's not uncommon, when debugging > > a particular problem, or discussing implementation of a new feature, > > to have a thread of discussion by CC'ing three or four developers, > > plus the HACKERS list for archiving and general interest. > > My mailer would have trouble sending just to the list and not to both. > To do list-only, the mailing list software would have to set the > Reply-To to be to the list. Sorry, but what an inappropriate answer coming from an IT professionnal. You MUA doesn't support answering to a mailing list? Why not consider upgrading to a modern MUA that _does_ support that functionality? Are we condemned to use obsolete software? Are we stuck in old habits for ever? > Marc had it set up that way a few times, but most didn't like it. In > fact, the big problem with that setup is that you can't easily reply > just to the poster. The "reply-to: list"? Oh, I see: that one almost made it on the pgsql-* lists... *shiver* As if subject mangling and annoying footers were not enough. > Most mailers have a 'reply to user' and 'reply to group' mode. Reply to > user goes only to the poster, while reply-to group goes to both. Hint: http://www.mutt.org > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 I wish people could also lose the preaching in their signatures. 1) what does "christ" mean to muslim or hindu or atheist pgsql users? 2) it might be offensive to them 3) why not talk about what we have in common (hint: databases), not the most divisive issue in the history of humanity: religion 4) were I Jesus, I wouldn't appreciate being held as a mere "backup" ;-) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: Can anyone explain how this works?
On Mon, 2 Apr 2001 [EMAIL PROTECTED] wrote: > 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; Understanding a few minutes' worth of the system tables hold will help a lot here--you can find that in the Developer's Guide. Eseentially, pg_class hold "classes" (ie tables, views, sequences, etc.) pg_attribute holds "Attributes" (ie fields). This query joins togetehr pg_class and pg_Attribute, showing you all attributes for a class with name = 'relation name'. attnum > 0 is perhaps the only odd part -- it has to do w/hiding certain system columns of tables that ordinary users don't realize are there are don't care about. -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(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] Casting numeric to text
Hans-Jürgen Schönig writes: > 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' Use the to_char() function. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Re: [SQL] Permissons on database
On Wed, Mar 07, 2001 at 03:40:44PM -0500, Roland Roberts wrote: > > "bk" == Boulat Khakimov <[EMAIL PROTECTED]> writes: > > bk> How do I grant permissions on everything in the selected > bk> databes? > > bk> GRANT doesnt take as on object database name nor does it > bk> accept wild chars > > Attached is some Perl code I wrote long ago to do this. This > particular code was done for Keystone, a problem tracking database and > it would do a "GRANT ALL". Modify it as needed. Last I checked it > worked with both PostgreSQL 6.5.x and 7.0.x A simple two-line shell script to apply any command to a list of tables: for i in `psql mydatabase -c '\dt' -P tuples_only | cut -f2 -d ' '` do psql mydatabase -c "grant all on $i to public"; done > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl These mailing list footers really suck. Please consider removing them. They reflect poorly on the general level of the pgsql-* lists. Instead do send a one-time "welcome" message containing all your "tips" when people subscribe to a list. Probably a lost cause but the subject mangling [GENERAL], [HACKERS] etc. (especially that one! a "hacker" should know how to filter his mail) really sucks too. Educate, don't stoop. Even Outlook Express has great filtering capabilities which don't require any subject mangling. -- slashdot: I miss my free time, Rob. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] open and closed paths ...
I am using PostgreSQL 7.0.3. I have tried the isopen() function but it
doesn't seem to work (or it is not fully implemented yet).
In my opinion the third record is a closed path but the isopen()
functions return f.
Have I done something wrong, have I got something wrong or is it a bug?
Hans
shop=# SELECT * FROM temppath;
fieldname
--
((1,3),(4,12))
((3,1),(2,8),(10,4))
((3,1),(2,8),(3,1))
((1,1),(2,2),(3,3))
(4 rows)
shop=# INSERT INTO temppath(fieldname) VALUES ('(1,1), (2,3)');
INSERT 51857 1
shop=# SELECT isopen(fieldname) FROM temppath;
isopen
f
f
f
f
f
(5 rows)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] open and closed paths ...
Hans-Jürgen Schönig writes: > I am using PostgreSQL 7.0.3. I have tried the isopen() function but it > doesn't seem to work (or it is not fully implemented yet). > In my opinion the third record is a closed path but the isopen() > functions return f. For no good reason apart from ancient tradition, paths enclosed in parentheses, like ((3,1),(2,8),(10,4)), are implicitly closed. To make an open path brackets should be used, like [(3,1),(2,8),(10,4)]. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Casting numeric to text
On Sun, 8 Apr 2001, Peter Eisentraut wrote: > Hans-Jürgen Schönig writes: > > > 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' > > Use the to_char() function. When would one want to use cast()? What is the difference between cast and :: ? After a quick look in the documentation I couldn't find anything.. -Cedar ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Need help with EXECUTE function
Attached is the (I think) corrected version.. If you do like I said and
cut the number in half you see fairly quickly why it didn't work. I'm
sending yours back so you can easily run a diff to see what I did. Let me
know if this (attached "cedars") works.
-Cedar
On Sat, 7 Apr 2001, Josh Berkus wrote:
> 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:
>
*snip*
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';
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_st
Re: [SQL] Double-nesting quotes?
Carefully. :) Try: where_string := ''WHERE client_name ~* '' || s_client || ; (I think I got that right.) The way I do it is to write it first as if I'm not inside a function and not double my single quotes. After I'm done, go back and double all of them.. you're on you own if you need to debug! -Cedar On Thu, 5 Apr 2001, Josh Berkus wrote: > Folks, > > I'm writing some functions that make serious use of the EXECUTE > functionality. However, I need to do some string comparisons inside the > execute statements ... how do I double-nest the single quote marks? > > EXAMPLE: > where_string := ''WHERE client_name ~* || s_client || ; > > -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 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] please help
It would be somewhat (very) useful to have something like this. We were toying with the idea of making some sort of system to figure out if a table is locked or not. In the end we decided to go with executing this asynchronously and after a given timeout ask the user if they would like to wait or cancel the request. Something like this may or may not work for you.. -Cedar On Fri, 6 Apr 2001, Loïc Bourgeois wrote: > Yes but the option NOWAIT say to the instruction SELECT ... FOR UPDATE > to not wait the unlock but to return the information the lines can't be > lock. > (Must retry late). > > > Peter Eisentraut wrote: > > > Loïc Bourgeois writes: > > > >> What is the equivalent of the oracle request: SELECT ... FOR UPDATE > >> NOWAIT, under PostGreSQL > > > > > > I don't know Oracle, but there doesn't seem to be such a command in > > PostgreSQL. If the table is already locked, the SELECT FOR UPDATE has to > > wait. > > > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
