Re: [SQL] VIEW or Stored Proc - Is this even possible?
Thanks for that Richard, I will try your suggestions. I'll let you know my results. Regards, Stephen. -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED]] Sent: 20 February 2003 11:08 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [SQL] VIEW or Stored Proc - Is this even possible? On Thursday 20 Feb 2003 10:09 am, [EMAIL PROTECTED] wrote: > Hello, > > Thanks for your suggestion I will look into this further. > > The reason this issue exists is to do with address details. What we have is > an address table based upon the PAF address structure. IE house number, > house name, street, locality etc. The software that we are using to perform > a mail merge will not remove blank lines from the address so we can end up > with an address label looking as: > > My House > > Main Street > > Any Town > County Ah - what an irritating bit of software. I'd be tempted to write a merged_address() function to return the whole thing as a multi-line field if your mailmerge can handle that. It would do something like: SELECT COALESCE(street || '\n','') || COALESCE(town || '\n','') || COALESCE(county || '\n','') FROM my_addr; and then trim the trailing '\n'. Failing that, seeing as you're only ever going to have a few fields to deal with you could define a mailing_list view which hard-coded address lines, but you're going to end up with nested CASE elements. -- Richard Huxton --- Copyright material and/or confidential and/or privileged information may be contained in this e-mail and any attached documents. The material and information is intended for the use of the intended addressee only. If you are not the intended addressee, or the person responsible for delivering it to the intended addressee, you may not copy, disclose, distribute, disseminate or deliver it to anyone else or use it in any unauthorised manner or take or omit to take any action in reliance on it. To do so is prohibited and may be unlawful. The views expressed in this e-mail may not be official policy but the personal views of the originator. If you receive this e-mail in error, please advise the sender immediately by using the reply facility in your e-mail software, or contact [EMAIL PROTECTED] Please also delete this e-mail and all documents attached immediately. Many thanks for your co-operation. BMW Financial Services (GB) Limited is registered in England and Wales under company number 01288537. Registered Offices : Europa House, Bartley Way, Hook, Hants, RG27 9UF -- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] point <-> polygon not supported?
Tomasz,
This works!
Thanks,
Scott Ding
-Original Message-
From: Tomasz Myrta [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 20, 2003 9:06 AM
To: Scott Ding
Cc: [EMAIL PROTECTED]
Subject: Re: [SQL] point <-> polygon not supported?
Scott Ding wrote:
> Tomasz,
>
> Thanks for the tip. What I want to do is something like the following:
>
> Select * from table where (table.geom <-> polygon('((3,3), (3,4),
(4,5),
> (5,4), (5,3))')) < 1.0);
>
> Table.geom is a point column.
I have never user geometric types...
I think, you can use some data type, which can be used for counting
distance, for example circle:
Select * from table where (circle(table.geom,0) <-> polygon('((3,3),
(3,4), (4,5), (5,4), (5,3))')) < 1.0);
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] EXCEPT Queries
On Thu, 20 Feb 2003 01:22:33 -0500, Mark Mitchell wrote: > Here is an example of what I'm currently doing. > > TABLE "A" > "SUBSCRIBER_NAME" | "ACCOUNT_NUMBER" > -- BOB | 01 JOE > | 02 > > TABLE "B" > "SUBSCRIBER_NAME" | "ACCOUNT_NUMBER" > -- BOB | 01 > > To dedup table "A" using the data in table "B" I could use the > following, except that the dedup takes place on the whole row when I > only want it to take place on the "ACCOUNT_NUMBER" column. > > SELECT > "A"."SUBSCRIBER_NAME" , "A"."ACCOUNT_NUMBER" FROM "A" EXCEPT > SELECT > "B"."SUBSCRIBER_NAME" , "B"."ACCOUNT_NUMBER" FROM "B" > > How about a SELECT DISTINCT ON? SELECT DISTINCT ON (account_number) subscriber_name, account_number FROM (SELECT 1 AS sort_order, subscriber_name, account_number FROM "A" UNION SELECT 2, subscriber_name, account_number FROM "B" ORDER BY sort_order) as tmp ORDER BY account_number; (Untested, but it follows a pattern I've learned.) -- Jeff Boes vox 269.226.9550 ext 24 Database Engineer fax 269.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise ---(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] Fw:
pgsql-sql >hello: > > when instal postgresql7.3.2 .in windows2000 >I have install Cygwin 1.3.3 >postgresql-7.3.1-1 >cygipc-1.09-2 > >all successful >but when I initdb -D /usr/local/pgsql/data >I got the following messages: >User > Posted: 2003-02-13 15:19 > > The files belonging to this database system will be owned by user "Administrator". >This user must also own the server process. > >The database cluster will be initialized with locale C. >create directory /data/base... ok >create directory /data/global... ok >create directory /data/pg_xlog... ok >create directory /data/pg_clog... ok >create template1 database in >/data/base/1...IpcSemaphoreCreate:semget(key=1,num=17,03600)failed: Function not >implemented >why ? >i have try many times > > > thanks > > > > > xhd >[EMAIL PROTECTED] > 2003-02-21 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Fw:
pgsql-sql >hello: > > when instal postgresql7.3.2 .in windows2000 >I have install Cygwin 1.3.3 >postgresql-7.3.1-1 >cygipc-1.09-2 > >all successful >but when I initdb -D /usr/local/pgsql/data >I got the following messages: >User > Posted: 2003-02-13 15:19 > > The files belonging to this database system will be owned by user "Administrator". >This user must also own the server process. > >The database cluster will be initialized with locale C. >create directory /data/base... ok >create directory /data/global... ok >create directory /data/pg_xlog... ok >create directory /data/pg_clog... ok >create template1 database in >/data/base/1...IpcSemaphoreCreate:semget(key=1,num=17,03600)failed: Function not >implemented >why ? >i have try many times > > > thanks > > > > > xhd >[EMAIL PROTECTED] > 2003-02-21 ---(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] PL/PGSQL EDITOR
Folks, One more note on the PostgreSQL SQL highlighting mode for Kate: Shane Wright, the author, has asked for feedback. So if you use it, please send feedback and requests to me and I'll forward them. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] How to drop all the sequences
I found sequences are not getting dropped when tables are dropped. It is too difficult to drop all sequences one by one manually. Is there any command to drop all sequences in a database? Looking for help. regards Jaisankar
[SQL] pg_func problem
Hi All, I am trying to use the function below, it works fine on my dev server running 7.2.3 but does not work on my production server running 7.1.2. (both on linux) Would anyone be able to shed some light on why this is the case. The error I get is: ERROR during compile of 'change_sup_ord_status' near line 19 parse error at or near "IF" I am running the following query against the function below: - Query: UPDATE supplier_order SET status = 'Pending' where id = 2003; CREATE FUNCTION change_sup_ord_status () RETURNS OPAQUE AS ' DECLARE num INT4; BEGIN IF OLD.status = ''Complete'' AND NEW.status != ''Complete'' THEN --Invalid option RAISE EXCEPTION ''This is an invlid status change ''; ELSIF OLD.status = ''Pending'' THEN IF NEW.status = ''Complete'' THEN UPDATE supplier_order_detail SET status=''Complete'' WHERE supplier_order_id = OLD.id AND status=''Pending''; ELSIF NEW.status = ''VOID'' OR NEW.status = ''Saved'' THEN SELECT count(*) INTO num FROM supplier_order_detail WHERE supplier_order_id = OLD.id AND status = ''Complete''; IF num > 0 THEN RAISE EXCEPTION ''Invalid change of status, some of the order has already been entered into stock''; END IF; END IF; ELSIF (OLD.status = ''VOID'') AND NEW.status = ''Complete'' THEN RAISE EXCEPTION ''Invalid change of status''; END IF; RETURN NEW; END;' LANGUAGE 'plpgsql'; Thanks in advance. Graham ---(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] once again, sorting with Unicode
There are various examples in the example source code section
of the postgres distribution, where you can find code you can
use to write exactly the kind of funtion you need. I can't
immediately include source code from us, but I can include
the gist of how the code works.
The basic idea is to convert the input data to byte values
which are in the right order. If the input data is unicode,
utf8, utf16, or whatever, you have to know what it is, so you
can convert the data to a meaningful byte stream which can be
evaluated just like an array of numbers would be. I.e. remove
bytes which indicate something to the encoding and convert
characters to their one byte values. E.g. if the data is UTF8,
it is one or two bytes long for ISO8859_1 (upto six bytes for
others), one byte for ascii and two bytes for ISO8859_1. You
need to convert it to a one byte long value so comparisons at
byte level will work. For pure unicode you just have to skip
every other byte.
1. Source code :
... various includes.
PG_FUNCTION_INFO_V1(sample_encoding_func);
Datum sample_encoding_func(PG_FUNCTION_ARGS) {
text * str;
text * result;
size_t len;
if (PG_ARGISNULL(0))
PG_RETURN_NULL();
str = PG_GETARG_TEXT_P(0);
len = VARSIZE(str) - VARHDRSZ;
... do your conversion thing, allocate memory for the
result and return the value, doing error checking as you
go.
}
Add the function to your db:
DROP FUNCTION sample_encoding_func (text);
CREATE FUNCTION sample_encoding_func (text) RETURNS text
AS 'sample_encoding_func.so'
LANGUAGE 'C' WITH (iscachable,isstrict);
You can create an index with:
create index dummyindex on usertable using btree (sample_encoding_func(username)
text_ops);
Troy
>
> At 20:16 19.2.2003, Troy K wrote:
> >You can generate indexes for your custom functions, though,
> >which will speed things up. This is what I've done, successfully.
>
> Sounds useful, do you have a demo of such a function?
>
> I can if all else fails sort the data using PHP but am not too fond of it
> when I have over 2000 rows or more as will be the case in other tables.
>
> Thanks all for the answers.
>
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
