Re: [SQL] once again, sorting with Unicode
You can generate indexes for your custom functions, though, which will speed things up. This is what I've done, successfully. Troy > > JBJ <[EMAIL PROTECTED]> writes: > > Furthermore I will have to be able to switch between is_IS and others to > > get the correct sort order ( russian sort order different from icelandic > > for example, simply due to the nature of the letters) live when the user > > selects a different language. > > Postgres can't do that at the moment :-(. You might be able to hack up > some custom functions to do something like > ORDER BY icelandic(text_col) > but it'll be messy, and probably slow. > > regards, tom lane > > ---(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 4: Don't 'kill -9' the postmaster
Re: [SQL] PL/PGSQL EDITOR
Folks, > > Well, Shane Wright just wrote an PostgreSQL syntax highlighter for Kate (part > > of the KDE project) Unfortunately, it's part of KDE 3.1, so if you're using a > > packaged distribution you'll have to wait for the next distribution version. Actually, Shane just sent me the XML file, and I got it to work with KDE 3.0.3. If anyone can think of an appropriate place in the suite of PostgreSQL sites, I'll post a copy of it there. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] simple join problem
On Wed, 19 Feb 2003, Matthew Nuzum wrote: > Sorry for the simple question, but I'm struggling with a join. > > I'm creating a view that will show data from 4 tables. The problem is, I > want the view to show a record for every entry in the "users" table, even if > there is no matching entry all or some of the other tables. > > Right now my view only shows records that have data in all 4 tables. I know > I've had this problem before and I know there's simple syntax, but I've only > done it with two tables in the join and I (apparently) can't remember the > correct syntax. > > Can anyone demonstrate the correct syntax for joining several tables in this > way? > > Here's my view definition: > SELECT > users.uid, users.loginid, users."password", users.title, > users.firstname, users.middlename, users.lastname, users.suffix, > users.organization, users.job_title, users_address.address1, > users_address.address2, users_address.address3, users_address.city, > users_address.state, users_address.zip, users_address.country, > users_email.email, users_phone.phone > FROM (((users > LEFT JOIN users_address ON ((users.uid = users_address.uid))) > LEFT JOIN users_email ON ((users.uid = users_email.uid))) > LEFT JOIN users_phone ON ((users.uid = users_phone.uid))) > WHERE (((users_address."primary" = 't'::bool) > AND (users_email."primary" = 't'::bool)) > AND (users_phone."primary" = 't'::bool)); The where clause undoes the LEFT JOIN. Maybe something like: SELECT ... FROM users LEFT JOIN (select * from users_address where primary='t') AS users_address ON (users.uid=users_address.uid) ... I'd thought about just changing the WHERE clause elements to something like: (users_address.uaid IS NULL OR users_address."primary" = 't'::bool) but that'll do the wrong thing if there are matching address but none are primary (it shouldn't happen presumably, but I don't see anything that stops it in the table descriptions -- I also don't know if there's an intention of having multiple primary addresses which I guess could happen unless primary is part of the pkey for those tables - which would prevent multiple secondaries, so I assume it isn't) ---(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] problem on truncate for v.7.3.2
Jack, > On version 7.3.2, 'truncate' can not be executed from pl/pgsql. But it > isn't stated on change notes. Is it a bug? No, it's a fix. TRUNCATE, VACUUM, ANALYZE, and REINDEX all perform COMMIT statements as part of their operation. If called within a function, this could cause part of the function to be committed while the rest failed, or even cause a fatal error. As such, these statements have been deliberately disabled within PL/pgSQL and SQL functions. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Passing arrays
Would it not be more reasonable to have array_dims return an int or int[]?
Has anyone ever seen an array that does not start at 1?
The other problem I find with array_dims returning text is when you have a
multi-dimentional array like this IIRC;
array_dims('{{asd,fgh,jkl},{zxc,vbn,mlk}}') = '[1:2][1:3]'
Which appears to mean that there the data is a 2 element array of a 3 element
array.
If the data was in an int array format like '{{1,2},{1,3}}' it would be dead
easy to get the dimentions of the array without messy text parsing. It would
be even better as '{2,3}' since a null element at the start of array is still
counted as an element so all arrays start from 1. A fairly simple function
could be made to "factor" all dimentions together to get a full sub_element
count, ie. 2x3 = 6 ... .
I think I will update my array_size function to handle this, but that means my
funtion has to deal with more messy text parsing to generate the int array for
multi dimentional arrays. I have up until now only been working with single
element arrays.
Here is an example of my array_size function for text arrays, I just tossed
this together from what I could remember, so it may not be exactly the same as
what I am using.
For V7.3 it should look somthing like this.
---%<...snip...
CREATE FUNCTION array_size(text[]) RETURNS int AS '
DECLARE
array ALIAS FOR $1;
dim int;
BEGIN
SELECT INTO dim
replace(split_part(array_dims(array),'':'',2),'']'',)::int ;
-- that was the messy stuff
IF dim IS NULL
THEN
dim := 0 ;
END IF;
RETURN dim;
END;
' LANGUAGE 'plpgsql';
---%<...snip...
For V7.2 it looked something like this, but it is more messy.
---%<...snip...
CREATE FUNCTION array_size(text[]) RETURNS int AS '
DECLARE
array ALIAS FOR $1;
dim int;
BEGIN
SELECT INTO dim
rtrim(ltrim(ltrim(array_dims($1),''[012345679''),'':''),'']'')::int ;
-- that was the messy stuff
IF dim IS NULL
THEN
dim := 0 ;
END IF;
RETURN dim;
END;
' LANGUAGE 'plpgsql';
---%<...snip...
I dropped these into a test DB, created test table and they do work so, here
are the results:
select *,array_size(destination) from size_test;
alias |destination| array_size
---+---+
alias1| {dest1} | 1
alias2| {dest2,dest1} | 2
alias3| {dest3,dest4} | 2
alias4| {dest3,dest4,dest5} | 3
alias5| {dest6,dest7} | 2
alias6| {dest3,dest7,dest4,dest5} | 4
alias7| | 0
I hope that this helps. You can over load the function by creating more of the
same function but using different array types for the input.
IE. array_size(int[]) instead of array_size(text[]).
Guy
Michael Weaver wrote:
There is a function array_dims(array) that returns the size of array.
It's not THAT useful as it returns a string like '[1:2]' <-( 1 lower, 2
upper bound.)
With a little bit of string processing you could get the size of the array.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
