Re: [SQL] generic crosstab ?
On 04/24/2012 02:42 PM, David Johnston wrote: You must specify the output record structure: SELECT crosstab(text, text) AS ( col1_name col1_type [, colN_name colN_type]* ) Whether this relates to the “materialization node” message you are receiving I have no idea. The error is because you are selecting from a set returning function in the target list rather than the from clause. It should be more like: SELECT * FROM crosstab(text, text) AS ( col1_name col1_type [, colN_name colN_type]* ) HTH, Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Generating Rows from a date and a duration
On 09/08/2010 08:24 AM, Tim Schumacher wrote: I'm kinda stuck situation, I have a timestamp which resambles a startdate and a duration in days and I want to bloat this, so I have a row for every day beginning from the startdate. I have created an example bellow, maybe I'm doing it on the wrong angle and you can come up with some better ideas: snip As you can see in my example, I'm already using it and this is my dilemma. Since I can not bring the values of the FROM-Table to the parameters of my function. Depending on how large your base table is, this might work for you: CREATE TABLE example ( id serial NOT NULL, startdate timestamp without time zone, duration int NOT NULL, CONSTRAINT pq_example_id PRIMARY KEY (id) ); insert into example(id,startdate,duration) values (1,'2010-09-03',4); insert into example(id,startdate,duration) values (2,'2010-09-03',6); CREATE OR REPLACE FUNCTION unroll_durations() RETURNS TABLE( example_id integer, duration_date date) AS $$ DECLARE rec1 record; rec2 record; BEGIN FOR rec1 IN SELECT id, startdate, duration FROM example LOOP FOR rec2 IN SELECT to_date(to_char(rec1.startdate,'-MM-DD'),'-MM-DD') + s.a as stockdate FROM generate_series(0, rec1.duration - 1) AS s(a) LOOP example_id:= rec1.id; duration_date := rec2.stockdate; RETURN NEXT; END LOOP; END LOOP; END; $$ LANGUAGE plpgsql; select * from unroll_durations(); example_id | duration_date +--- 1 | 2010-09-03 1 | 2010-09-04 1 | 2010-09-05 1 | 2010-09-06 2 | 2010-09-03 2 | 2010-09-04 2 | 2010-09-05 2 | 2010-09-06 2 | 2010-09-07 2 | 2010-09-08 (10 rows) HTH, Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support signature.asc Description: OpenPGP digital signature
Re: [SQL] strangest thing happened
On 07/07/2010 12:59 PM, John wrote:
I am the only developer, DBA etc.. for a small project. Today (yesterday was
everything was perfect) many of the sequence numbers fell behind what is the
actual PK value. For example the invoice PK sequence current value = 1056
but the table PK was 1071. Nobody (other than myself) knows how to
edit/access the postgres server. So
1. Does anyone know how this could have happened?? Other than human
interaction.
I've never heard of this happening. Are you certain nothing bypassed the
sequence and directly inserted a PK value?
2. Does anyone have a script to reset the sequences to match the tables?
Not heavily tested, but something like this might do the trick:
8--
CREATE OR REPLACE FUNCTION adjust_seqs(namespace text)
RETURNS text AS $$
DECLARE
rec record;
startvalbigint;
sql text;
seqname text;
BEGIN
FOR rec in EXECUTE 'select table_name, column_name, column_default
from information_schema.columns
where table_schema = ''' || namespace || '''
and column_default like ''nextval%''' LOOP
seqname := pg_get_serial_sequence(rec.table_name, rec.column_name);
sql := 'select max(' || rec.column_name || ') + 1 from ' ||
rec.table_name;
EXECUTE sql INTO startval;
IF startval IS NOT NULL THEN
sql := 'ALTER SEQUENCE ' || seqname || ' RESTART WITH ' ||
startval;
EXECUTE sql;
RAISE NOTICE '%', sql;
END IF;
END LOOP;
RETURN 'OK';
END;
$$ LANGUAGE plpgsql STRICT;
select adjust_seqs('public');
8--
HTH,
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, Support
signature.asc
Description: OpenPGP digital signature
Re: [SQL] [GENERAL] Encrypted column
Marko Kreen wrote:
On 6/5/07, Brian Mathis [EMAIL PROTECTED] wrote:
pgcrypto also supports md5, so I'm not sure what you're referring to
here.
digest(psw, 'md5') vs. crypt(psw, gen_salt('md5'))
As I already mentioned, *salting* before you hash is a very
important step. I'm not sure if you saw that in my post. Without a
salt, it's trivial to generate a list of all combinations of md5'd
strings and their results, up to reasonable lengths. Then it would be
very simple to look up each hash and get the original text. With a
salt, you need to generate all possible md5s for all possible salts --
a much harder task.
I dont think its practical method tho'. Rather, when doing
dictionary-based or bruteforce attack, then if hashes do not
have salts you attack them all at once.
But if they have salts then for each word you try you need to
hash it for each salt. Which basically gives the effect that
each hash needs to be attacked separately.
In case of attacking one hash the salt does not matter,
only the algorithm counts then. In that case as i said,
event salted md5 is weaker than des-crypt.
The best method as far as I understand it is HMAC
(http://www.faqs.org/rfcs/rfc2104.html).
It has some significant cryptanalysis behind it to ensure it does not
leak information that would compromise the password. Even MD5 and SHA1,
which have been shown to have certain weaknesses, are not at issue when
used with HMAC (see, for example, section 3.1.1 of
http://www.apps.ietf.org/rfc/rfc4835.html)
The way you would use HMAC is:
1. generate a random token, whatever length you want (the salt)
2. use HMAC (implemented with either md5 or sha1 or something newer) to
hash the salt with the password
3. store the salt and the resulting HMAC hash
4. on login, calculate the HMAC of the token using the provide password,
and compare to the stored hash
pgcrypto appears to support HMAC. It is also relatively easy to
implement on top of the built in md5 function if you'd rather not
install pgcrypto. And I'm sure there are HMAC functions available that
could be used in PL/Perl and/or PL/Python.
Joe
---(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] Very large IN-clause is slow, but how to rewrite it?
Tom Lane wrote: Richard Jones [EMAIL PROTECTED] writes: I've been profiling a PG database / mix of applications and found that one statement which takes a very long time to execute is: PG 8.2 does better with long IN-lists ... although if the list is so long as to be fetching a significant fraction of the table, you'll still have problems. In that case I'd advise putting the values into a temp table, ANALYZEing same, and doing WHERE foo IN (SELECT x FROM tmp_table). If 8.2, what about ... WHERE foo IN (select x from (values (1$),(2$),...,(N$)) as t(x)) ? It would be interesting to see how that compares performance-wise. Joe ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] psql -F TAB
T E Schmitz wrote: Alvaro Herrera wrote: T E Schmitz wrote: I have written a shell script to export data: psql -A -t -U $DBUSER -d $DB -c $QUERY -F ' ' Currently, I am using spaces as field separator but what I really want is tabs. How can I specify a TAB character with the -F option? Sorry for having gone off-topic. I was just hoping something like \t could be passed, too. Try: psql -A -t -U $DBUSER -d $DB -c $QUERY -F $'\t' see: man bash HTH, Joe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] query; check for zero and floats
[EMAIL PROTECTED] wrote: I'm trying to do a simple query and I'm not sure how to get it to work: SELECT SUM(x0 + y0 + z0) / SUM(x2 + y2) AS A1, SUM(x1 + y1 + z1) / SUM(x3 + y3) AS A2 FROM test Problems: 1. All variables are integers. When it does the division, it returns an integer, but I want a float. (I've tried numerous things to no avail) 2. If SUM(x2 + y2) == 0; there is a divide by zero error. How do I check and pass on zeroes? A bit ugly, but seems to work: create table t1 (g int, x0 int, y0 int, z0 int, x2 int, y2 int); insert into t1 values (0, 1,1,1,1,-1); insert into t1 values (0, 1,1,1,-1,1); insert into t1 values (1,1,1,1,1,1); insert into t1 values (1,2,3,4,5,6); SELECT g, case when SUM(x2::float8 + y2::float8) = 0 then 0 else SUM(x0::float8 + y0::float8 + z0::float8) / SUM(x2::float8 + y2::float8) end AS A1 from t1 group by g; g |a1 ---+--- 1 | 0.923076923076923 0 | 0 (2 rows) HTH, Joe ---(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] ARRAY() returning NULL instead of ARRAY[] resp. {}
Tom Lane wrote:
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= [EMAIL PROTECTED] writes:
By analogy, array_upper('{}'::TEXT[], 1) should return 0 instead of
NULL.
No, that doesn't follow ... we've traditionally considered '{}' to
denote a zero-dimensional array. A 1-D array of no elements is
'[1:0]={}', just as Joe shows ... or at least it would be except
for an overenthusiastic error check:
regression=# select '[1:0]={}' :: int[];
ERROR: upper bound cannot be less than lower bound
I think this should be a legal boundary case. In general, it should be
possible to form zero-size arrays of any number of dimensions.
I've been playing with exactly this over the weekend. Of course, as
usual, the devil is in the details. For instance, using the above
notation, how would I specify a zero-element 1D array starting at a
lower bound index of 0? The answer following the above pattern would be:
select '[0:-1]={}'::int[];
You could not use '[0:0]={}'::int[], because that would be a one-element
array. I propose the following instead:
regression=# select '[1:]={}' :: int[];
int4
--
{}
(1 row)
regression=# select array_dims('[1:]={}' :: int[]);
array_dims
[1:]
(1 row)
In other words, an missing upper bound indicates zero elements.
Now the next question; what does a 2D zero-element array look like? I
think this makes sense:
regression=# select '[1:2][1:]={{},{}}'::int[];
int4
--
{}
(1 row)
Except (I think) array_out() should probably output something closer to
the input literal. Any thoughts on this?
Joe
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
Bruce Momjian wrote:
Joe Conway wrote:
Any thoughts on how this should be handled for an empty 1D array?
No one responed to this email, so I will try. Is this the one
dimmentional array you were talking about?
test= select array_dims('{}'::integer[]);
array_dims
(1 row)
In this case, what you get is actually a dimensionless array. Literally,
you get this:
if (nitems == 0)
{
/* Return empty array */
retval = (ArrayType *) palloc0(sizeof(ArrayType));
retval-size = sizeof(ArrayType);
retval-elemtype = element_type;
PG_RETURN_ARRAYTYPE_P(retval);
}
I.e. the array structure is allocated, the size is set (which is
required since arrays are varlena), and the element type is initialized.
There is no initialization of ndim, ARR_DIMS(), or ARR_LBOUND().
In this case, since there are no dimensions, array_dims() probably does
the right thing by returning NULL.
Why is [1:0] wrong to return?
I'm not sure it is wrong -- it just seems a bit strange. The difference
is that in order to return an empty *one-dimensional* array, ndim,
ARR_DIMS(), and ARR_LBOUND() are all appropriately set (by the patched
code). Basically, ndim == 1, ARR_DIMS() is a single element int array (C
array that is) indicating 0 elements for dimension 1, and ARR_LBOUND()
is a single element int array indicating a lower bound of 1. This leads
to the array_dims() return value of [1:0]. The value 1 is unquestionably
correct for the lower bound index, but what should be reported for the
upper bound? We can't return [1:1], because that would indicate that we
have one element.
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
Tom Lane wrote:
I think he's got a good point, actually. We document the ARRAY-with-
parens-around-a-SELECT syntax as
The resulting one-dimensional array will have an element for
each row in the subquery result, with an element type matching
that of the subquery's output column.
To me, that implies that a subquery result of no rows generates a
one-dimensional array of no elements, not a null array.
OK, looks like I'm outnumbered.
But as far as I know, we have never had a way to produce a
one-dimensional empty array. Empty arrays thus far have been dimensionless.
Assuming we really want an empty 1D array, I created the attached patch.
This works fine, but now leaves a few oddities to be dealt with, e.g.:
regression=# select array_dims(array(select 1 where false));
array_dims
[1:0]
(1 row)
Any thoughts on how this should be handled for an empty 1D array?
The point Markus is complaining about seems like it should
be easily fixable.
Well, easily is a relative term. My Postgres hacking neurons have
gotten kind of rusty lately -- but then maybe that was your underlying
point ;-)
Joe
Index: src/backend/executor/nodeSubplan.c
===
RCS file: /cvsroot/pgsql/src/backend/executor/nodeSubplan.c,v
retrieving revision 1.69
diff -c -r1.69 nodeSubplan.c
*** src/backend/executor/nodeSubplan.c 6 May 2005 17:24:54 - 1.69
--- src/backend/executor/nodeSubplan.c 26 May 2005 18:52:16 -
***
*** 215,220
--- 215,221
ListCell *pvar;
ListCell *l;
ArrayBuildState *astate = NULL;
+ Oid element_type = planstate-ps_ResultTupleSlot-tts_tupleDescriptor-attrs[0]-atttypid;
/*
* We are probably in a short-lived expression-evaluation context.
***
*** 259,268
*
* For EXPR_SUBLINK we require the subplan to produce no more than one
* tuple, else an error is raised. For ARRAY_SUBLINK we allow the
! * subplan to produce more than one tuple. In either case, if zero
! * tuples are produced, we return NULL. Assuming we get a tuple, we
! * just use its first column (there can be only one non-junk column in
! * this case).
*/
result = BoolGetDatum(subLinkType == ALL_SUBLINK);
*isNull = false;
--- 260,269
*
* For EXPR_SUBLINK we require the subplan to produce no more than one
* tuple, else an error is raised. For ARRAY_SUBLINK we allow the
! * subplan to produce more than one tuple. In the former case, if zero
! * tuples are produced, we return NULL. In the latter, we return an
! * empty array. Assuming we get a tuple, we just use its first column
! * (there can be only one non-junk column in this case).
*/
result = BoolGetDatum(subLinkType == ALL_SUBLINK);
*isNull = false;
***
*** 432,458
}
}
! if (!found)
{
/*
* deal with empty subplan result. result/isNull were previously
! * initialized correctly for all sublink types except EXPR, ARRAY,
* and MULTIEXPR; for those, return NULL.
*/
if (subLinkType == EXPR_SUBLINK ||
- subLinkType == ARRAY_SUBLINK ||
subLinkType == MULTIEXPR_SUBLINK)
{
result = (Datum) 0;
*isNull = true;
}
}
- else if (subLinkType == ARRAY_SUBLINK)
- {
- Assert(astate != NULL);
- /* We return the result in the caller's context */
- result = makeArrayResult(astate, oldcontext);
- }
MemoryContextSwitchTo(oldcontext);
--- 433,459
}
}
! if (subLinkType == ARRAY_SUBLINK)
! {
! if (!astate)
! astate = initArrayResult(element_type, oldcontext);
! /* We return the result in the caller's context */
! result = makeArrayResult(astate, oldcontext);
! }
! else if (!found)
{
/*
* deal with empty subplan result. result/isNull were previously
! * initialized correctly for all sublink types except EXPR
* and MULTIEXPR; for those, return NULL.
*/
if (subLinkType == EXPR_SUBLINK ||
subLinkType == MULTIEXPR_SUBLINK)
{
result = (Datum) 0;
*isNull = true;
}
}
MemoryContextSwitchTo(oldcontext);
***
*** 925,930
--- 926,932
ListCell *l;
bool found = false;
ArrayBuildState *astate = NULL;
+ Oid element_type = planstate-ps_ResultTupleSlot-tts_tupleDescriptor-attrs[0]-atttypid;
/*
* Must switch to child query's per-query memory context.
***
*** 1010,1016
}
}
! if (!found)
{
if (subLinkType == EXISTS_SUBLINK)
{
--- 1012,1033
}
}
! if (subLinkType == ARRAY_SUBLINK)
! {
! /* There can be only one param... */
! int paramid = linitial_int(subplan-setParam);
! ParamExecData *prm = (econtext-ecxt_param_exec_vals[paramid]);
!
! prm-execPlan = NULL;
!
! if (!astate)
! astate = initArrayResult(element_type, oldcontext);
!
! /* We build the result in query context so it won't disappear */
! prm-value
Re: [SQL] PGCrypto: Realworld scenario and advice needed
Moran.Michael wrote: My initial attack plan was to do the following: 1. Call decrypt() with the old-passphrase to decrypt each table's existing data. 2. Temporarily store the decrypted data in temp tables. 3. Delete all rows of encrypted data from the original tables -- thereby clearing the tables of all data encrypted with the old passphrase. 4. Call encrypt() with the new passphrase to encrypt all data in the temp tables -- thereby repopulating the production tables with data encrypted with the new passphrase. 5. Blow away the temp tables. But this seems like a tedious procedure. Is there any simple way to update ALL existing encrypted data with a new passphrase, assuming you know the old passphrase and encryption type (i.e. AES, Blowfish, etc.) without having to go through the 5-step process mentioned above? Why not use a single UPDATE command, e.g. something like: UPDATE tbl SET f1 = encrypt(decrypt(f1, 'oldkey', 'aes'), 'newkey', 'aes'); Joe ---(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] Calling a table residing in another database from Plpgsql
[EMAIL PROTECTED] wrote:
SELECT * from dblink('host=192.168.0.50 dbname=AK1 user=akteam','Select
userid from user_reg') as t (userid integer);
I am getting an error as ERROR: function dblink(unknown, unknown)
does not exist
Have you installed the dblink functions into your database? See
README.dblink.
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Create Calendar
Muhyiddin A.M Hayat wrote: How to create Calendar using Function/View. For example i would like to display date 2004-12-01 to 2004-12-20. date -- 2004-12-01 2004-12-02 2004-12-03 2004-12-04 2004-12-05 .. .. 2004-12-20 -- Use in Postgres 7.4.x and earlier. -- In Postgres 8.0.0 generate_series() is a built-in function CREATE OR REPLACE FUNCTION generate_series(int, int) RETURNS setof int AS ' BEGIN FOR i IN $1..$2 LOOP RETURN NEXT i; END LOOP; RETURN; END; ' LANGUAGE plpgsql; select '2004/12/01'::date + f1 from generate_series(0, 19) as t(f1); ?column? 2004-12-01 2004-12-02 2004-12-03 2004-12-04 2004-12-05 [...] 2004-12-20 (20 rows) HTH, Joe ---(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] PL/pgSQL multidimension (matrix) array in function
Sergio Fantinel wrote:
I found how to use, inside a PL/pgSQL function, a two-dimensions array
(matrix).
There is a limitation: the number of the 'columns' of the matrix is
fixed at declaration time (in DECLARE section) and you need to manually
initialize all the elements in the first 'row' of the matrix.
You should use '{}' to initialize the array to empty. See below for an
example:
CREATE OR REPLACE FUNCTION testarray (integer, integer) RETURNS SETOF
integer[] AS'
DECLARE
n alias for $1;-- number of rows is passed as argument
i INTEGER;
j integer;
k alias for $2; -- matrix columns number
a integer[];
begin
for i in 1..n loop
a := ''{}''; -- create empty array
for j in 1..k loop
a := a || i;
return next a;
end loop;
end loop;
return;
end;
'LANGUAGE 'plpgsql';
regression=# select * from testarray(2,3);
testarray
---
{1}
{1,1}
{1,1,1}
{2}
{2,2}
{2,2,2}
(6 rows)
HTH,
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] array_in: '{}}'::text[]
Markus Bertheau wrote:
Is there a reason the array_in parser accepts additional closing braces
at the end?
oocms=# SELECT '{}}'::text[];
text
--
{}
(1 )
Hmmm, I was *about* to say that this is fixed in cvs (and indeed, the
array_in parser is significantly tightened up compared to previous
releases), but unfortunately, there is still work to be done :(
regression=# SELECT '{}}'::text[];
text
--
{}
(1 row)
regression=# select version();
version
-
PostgreSQL 8.0.0beta1 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7)
(1 row)
Look for a fix soon, at a cvs repository near you
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] reply to setting
Greg Stark wrote: Just as a side comment, one trick I found very helpful in my mail filters is to treat any message with one of my message-ids in the references as a personal message as far as mail notifications. This way I get notifications for any message on a thread following a post of my own. Interesting idea -- thanks! Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] SQL Challenge: Arbitrary Cross-tab
Josh Berkus wrote: The Problem: for each case there are from zero to eight timekeepers authorized to work on the case, out of a pool of 150 timekeepers. This data is stored vertically: authorized_timekeepers: case_id | timekeeper_id 213447 | 047 132113 | 021 132113 | 115 132113 | 106 etc. But, a client's e-billing application wants to see these timekeepers displayed in the following horizontal format: case_id | tk1 | tk2 | tk3 | tk4 | tk5 | tk6 | tk7 | tk8 213447 | 047 | | | | | | | | 132113 | 021 | 115 | 106 | 034 | 109 | 112 | 087 | etc. Order does not matter for timekeepers 1-8. This is a daunting problem because traditional crosstab solutions do not work; timekeepers 1-8 are coming out of a pool of 150. Can it be done? Or are we going to build this with a row-by-row procedural loop? (to reiterate: I'm not allowed to use a custom aggregate or other PostgreSQL advanced feature) This is pretty much exactly how contrib/tablefunc's crosstab (non-hashed version; crosstab(sourcesql, ncols)) works. If you really need it to be portable, though, application layer procedural code is likely to be the easiest and fastest way to go. crosstab just wraps the procedural code in an SRF for you. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] reply to setting
Bruno Wolff III wrote: On Sat, Aug 07, 2004 at 09:33:08 +0530, Kenneth Gonsalves [EMAIL PROTECTED] wrote: any reason why the default reply-to on this list should not be set to the list? I keep replying to postings only to find later that the reply goes to the OP and not to the list. reply-all button results in needless duplication The duplication is needless. Direct replies very often get to the recipient ^ is *not* needless? faster than ones sent through the lists. It is also possible that the direct replies might be handled differently by the recipient (e.g. a filter may put them in different folders). This is very true. In fact, I get mildly annoyed when people *don't* include the direct reply to me, because I very actively filter/redirect my mail. Replies directly to me are pretty much guaranteed to be seen quickly, but the ones that go to the list might get lost among the hundreds of posts that go into my postgres inbox every day. I think many other people do something similar. Recipients that prefer not to get separate copies can indicate that desire by including an appropiate mail-followup-to header. Also true. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Wierded error in recursive function; debugging ideas?
Josh Berkus wrote: [2] Wrong datatype for second argument in call to in_array SQL: SELECT sf_event_decendants(66645,111) Are you sure this message isn't coming from some PHP middleware, e.g. peardb or something. See: http://us2.php.net/manual/en/function.in-array.php Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] function expression in FROM may not refer to other relations
Philippe Lang wrote: But the same query with a parameter returns an error: select id, usr, code, line1, line2 from tbl, get_lines(code); -- ERROR: function expression in FROM may not refer to other relations of same query level This is as expected and required -- you cannot refer to other FROM clause relations. I believe SQL99 defines a clause (LATERAL IIRC) that would allow this, but it hasn't been implemented yet. Is there another way to run this query and get: idusr code line1 line2 -- 1 one 1 A B 2 two 2 Z Z 3 three1 A B Whats wrong with just using CASE: select id, usr, code, case when code = 1 then 'A' else 'Z' end as line1, case when code = 1 then 'A' else 'Z' end as line2 from tbl; id | usr | code | line1 | line2 +---+--+---+--- 1 | one |1 | A | A 2 | two |2 | Z | Z 3 | three |1 | A | A (3 rows) Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] ERROR: Cross-database references are not implemented
Theo Galanakis wrote: How do I explicidly create a cross-database reference? Mind you these databases are on the same server. In MSSQL you could do that through enterprise manager. Any help would be appreciated. You cannot do cross database references per se, but you can use schemas, which roughly behave like individual databases do in MSSQL. See: http://www.postgresql.org/docs/7.4/static/ddl-schemas.html If you really need cross-database queries, see contrib/dblink (which would also work across servers), but it isn't as flexible as using schemas. HTH, Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] contrib/tablefunc crosstab
Caleb Simonyi-Gindele wrote: My problem is that the rows are populated from right to left irrespective of the column. So the $561.99 in vet1 actually belongs to vet2. There are never any blank cells in the middle or start of a row - they always come at the end. How do I get the amount for that date into the appropriate column? You either need to be sure there are no gaps in your source rows (provide one row for every category for each group, e.g. by doing an outer join) or use the hashed version of the function. Search the README for: crosstab(text source_sql, text category_sql) That is the hashed version that will do what you're looking for. HTH, Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Converting a plperlu function to a plpgsql function
Devin Whalen wrote:
First line:
my @active_tables=split(/,/,$tables);
Is there anyway to split a variable like the perl split above?
I'm no perl guru, but in 7.4 I believe this does what you're looking for:
regression=# select string_to_array('1,2,3',',');
string_to_array
-
{1,2,3}
(1 row)
Second line:
if ($r=~/^-([0-9]?)([A-z_]+)/)
{
my $locid = $1;
my $table = $2;
Not sure about this one. Hopefully someone else can chime in. Maybe a
little less efficient, but it seems like it would be easy enough to
parse when true.
HTH,
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Empty array IS NULL?
Markus Bertheau wrote:
is the empty array representable in PostgreSQL, and is it
distinguishable from NULL?
Yes, and yes.
regression=# select '{}'::int[];
int4
--
{}
(1 row)
regression=# select NULL::int[];
int4
--
(1 row)
Since NULL array elements are not currently supported, attempting to
construct an array with a NULL element results in NULL, not an empty array.
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Empty array IS NULL?
Markus Bertheau wrote:
How do I specify an empty array with the standard syntax?
Actually, up until the 7.4 release, the array literal syntax was the
*only* syntax (i.e. '{...}'). The newer array constructor expression
(i.e. ARRAY[...]) does not yet support creating empty arrays -- there
are some non-trivial technical difficulties to be solved. Unfortunately
that won't change in the upcoming 7.5 either :( -- perhaps for 7.6.
Joe
---(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] Empty array IS NULL?
Markus Bertheau wrote:
How do I specify an empty array with the standard syntax?
Actually, up until the 7.4 release, the array literal syntax was the
*only* syntax (i.e. '{...}'). The newer array constructor expression
(i.e. ARRAY[...]) does not yet support creating empty arrays -- there
are some non-trivial technical difficulties to be solved. Unfortunately
that won't change in the upcoming 7.5 either :( -- perhaps for 7.6.
Joe
---(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 8: explain analyze is your friend
Re: [SQL] pivot-like transformation
Torsten Lange wrote: Hello, I have a table with measurement values and columns like this: analyses(id, sample_id, parameter[temperatur...], value, unit[?C...], error) With PL/PgSQL at the end I want try to perform a pivot-like arrangement of these data: sample_id|Temperature [?C]|parameter2 [mg/L]|...|parameterN [L/year] -++-+---+--- 5| 23.00|0.036|...| 35.1 Not a direct answer with respect to plpgsql, but for pivot table functionality see the contrib/tablefunc function crosstab(). I also found very few sources about handling of arrays and how to fill them up with query results... things like this. Does anyone know a more comprehensive source? Have you looked at the online docs? http://www.postgresql.org/docs/7.4/static/arrays.html http://www.postgresql.org/docs/7.4/static/functions-array.html http://www.postgresql.org/docs/7.4/static/functions-comparisons.html http://www.postgresql.org/docs/7.4/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS HTH, Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Last day of month
Greg Sabino Mullane wrote:
How to find the last sunday/mon/sat of any given month.
There is probably a smoother way to do it, but here is a
quick little function to do what you ask. Feed it a date
and a number, where 0 is Sunday, 1 is Monday, etc.
How about this:
regression=# select date_trunc('month', current_date + '1 month'::interval);
date_trunc
-
2004-03-01 00:00:00
(1 row)
Joe
---(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] function returning array
Dennis wrote:
pg 7.4.1
I should have listed the source for the function. Here is a simplified
parseString function and the foo that calls it.
dennis=# create or replace function parseString (varchar, varchar)
dennis-# RETURNS varchar[] AS '
dennis'# DECLARE
dennis'# pParsed varchar[];
Make that last line:
pParsed varchar[] := ''{}'';
That initializes pParsed to an *empty* array. Otherwise pParsed is NULL,
and when you attempt to extend a NULL array, e.g. pParsed[1] =
''blah'' you still get NULL. It is similar to this:
regression=# select NULL || 'blah';
?column?
--
(1 row)
HTH,
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] function returning array
Dennis wrote:
I am trying to return an array from a function and don't seem to be
having luck. The function seems to work fine, but if I do assignment to
an array variable, I get null in the array elements
DECLARE
results varchar[];
tmpv varchar;
BEGIN
-- now call func that returns varchar[]
results := parseString(''abc,def,ghi'','','');
tmpv := results[1];
RAISE NOTICE '' tmpv = % '',tmpv;-- tmpv will be null.
END;
How is parseString() defined? What Postgres version?
Joe
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] SQL Standatd
Bruce Momjian wrote: Martin Marques wrote: Also, for some reason I can't get to sqlstandards.org. Is there any other place where I can get the SQL200X docs? Not sure. See: http://www.wiscorp.com/sql/sql_2003_standard.zip Joe ---(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] [pgsql-advocacy] SQL Spec Compliance Questions
Josh Berkus wrote: 4.16.2 Referenceable tables, subtables, and supertables A table BT whose row type is derived from a structured type ST is called a typed table. Only a base table or a view can be a typed table. A typed table has columns corresponding, in name and declared type, to every attribute of ST and one other column REFC that is the self-referencing column of BT; let REFCN be the I really don't quite understand this, but I don't think we have it ;-) Was the SQL99 Committee smoking crack, or what?What the heck is that *for*? After re-reading it, I think it is related to (or at least similar to) the work Tom is currently doing to allow composite types as table attributes. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Array_append does not work with Array variables in PL/pgSQL?
Josh Berkus wrote:
v_vals TEXT[];
n_vals TEXT[];
try:
v_vals TEXT[] := ''{}'';
n_vals TEXT[] := ''{}'';
You have to initialize the array to something non-null, even if that be
an empty array (note that there is a difference). When trying to append
an element to a NULL valued array, you wind up with a NULL result. It is
similar to:
regression=# select (NULL || 'abc') is null;
?column?
--
t
(1 row)
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Array_append does not work with Array variables in PL/pgSQL?
Josh Berkus wrote: BTW, did you get my e-mail to Hackers about ARRAY[] IS NULL? I saw it, but I've been too swamped to really read it. I'll try to carve out some time this afternoon. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] [pgsql-advocacy] SQL Spec Compliance Questions
Josh Berkus wrote: 6) SQL-99 Distinct Types 7) SQL-99 Structured Types 9) SQL-99 Collection Types 10) SQL-99 Typed tables and views My answers: 6), 7) Not sure what these are. Here's the section in SQL99: 4.8 User-defined types A user-defined type is a schema object, identified by a user-defined type name. The definition of a user-defined type specifies a number of components, including in particular a list of attribute definitions. Although the attribute definitions are said to define the representation of the userdefined type, in fact they implicitly define certain functions (observers and mutators) that are part of the interface of the user-defined type; physical representations of user-defined type values are implementation-dependent. The representation of a user-defined type is expressed either as a single data type (some predefined data type, called the source type), in which case the user-defined type is said to be a distinct type, or as a list of attribute definitions, in which case it is said to be a structured type. So if I read that correctly, they are user defined types, that are either scalar (distinct) or composite (structured) -- so I'd say yes. 9) ??? From SQL99: 4.11 Collection types A collection is a composite value comprising zero or more elements each a value of some data type DT. If the elements of some collection C are values of DT, then C is said to be a collection of DT. The number of elements in C is the cardinality of C. The term element is not further defined in this part of ISO/IEC 9075. The term collection is generic, encompassing various types (of collection) in connection with each of which, individually, this part of ISO/IEC 9075 defines primitive type constructors and operators. This part of ISO/IEC 9075 supports one collection type, arrays. We are not yet fully compliant with SQL99 arrays, but not too far off either, I think. We have some extensions to SQL99 behavior, that would require breaking backward compatibility in order to do away with them. For example, SQL99 arrays *always* start with a lower bound of 1, if I read the spec correctly. Also multidimensional arrays in SQL99 are arrays of arrays, which is not quite the same as our multidimensional arrays. 10) Also not sure SQL99: 4.16.2 Referenceable tables, subtables, and supertables A table BT whose row type is derived from a structured type ST is called a typed table. Only a base table or a view can be a typed table. A typed table has columns corresponding, in name and declared type, to every attribute of ST and one other column REFC that is the self-referencing column of BT; let REFCN be the column name of REFC. The declared type of REFC is necessarily REF(ST) and the nullability characteristic of REFC is known not nullable. If BT is a base table, then the table constraint UNIQUE(REFCN) is implicit in the definition of BT. A typed table is called a referenceable table. A self-referencing column cannot be updated. Its value is determined during the insertion of a row into the referenceable table. The value of a system-generated selfreferencing column and a derived self-referencing column is automatically generated when the row is inserted into the referenceable table. The value of a user-generated self-referencing column is supplied as part of the candidate row to be inserted into the referenceable table. I really don't quite understand this, but I don't think we have it ;-) HTH, Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Encoding bytea
Achilleus Mantzios wrote:
is there a way to encode a bytea in such a way that the resulting
text stream be readily available (\\ escaped for unprintable chars) for
usage in an insert statement?
None of base64,hex,escape options in encode() seem to produce
anything close.
This is meant to be used with generating insert statements in xml files
for remote processing.
Is this what you need?
create table t(f bytea);
insert into b values ('a\\003\\000\\001b');
create or replace function bytea2text(bytea) returns text as '
begin
return $1;
end;
' language plpgsql;
regression=# select 'insert into t values(' ||
quote_literal(bytea2text(f)) || ');' from t;
?column?
insert into t values('a\\003\\000\\001b');
(1 row)
regression=# insert into t values('a\\003\\000\\001b');
INSERT 292656 1
HTH,
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] returning a recordset from PLpg/SQL
Tom Lane wrote:
Stephan Szabo [EMAIL PROTECTED] writes:
On Tue, 2 Mar 2004, Terence Kearns wrote:
Well I haven't yet done anything because I couldn't get anything to
compile which returned SETOF RECORD..
As a starting point, SETOF RECORD is different from SETOF RECORD given
PostgreSQL's fold case to lower case for unquoted names.
Also, you can hardly expect a function to return a rowtype that doesn't
even exist until the function executes --- how the heck is the parser
supposed to make sense of the calling query? So the execute create
type part of this is nonsense, I'm afraid. The SETOF RECORD mechanism
will let you return a rowtype that is not known fully at the time the
function is written, but the rowtype does have to be known when the
calling query is parsed.
You might be able to replace the CREATE TYPE with an anonymous record
type in the calling query:
select ...
from details_for_profile(...) as x(doc_id int4,
doc_title varchar(256),
...);
A small improvement is to do a two-step process. From your app, you
first SELECT a function call that returns an SQL statement as a text
string, specific to att_data_type. Then you execute that as a second
step. For example:
--8--
create table hdr(hid int primary key, context text);
insert into hdr values(1, 'test1');
insert into hdr values(2, 'test2');
create table att(aid int primary key, attname text, atttype text);
insert into att values (1, 'test_date', 'timestamp');
insert into att values (2, 'height', 'float8');
insert into att values (3, 'width', 'float8');
insert into att values (4, 'color', 'text');
create table det(
did int primary key,
hid int references hdr,
aid int references att,
val text
);
insert into det values(1,1,1,'15-mar-2004');
insert into det values(2,1,2,'3.14159');
insert into det values(3,1,3,'2.8');
insert into det values(4,1,4,'blue');
insert into det values(5,2,1,'16-mar-2004');
insert into det values(6,2,2,'2.34');
insert into det values(7,2,3,'3.28');
insert into det values(8,2,4,'red');
create or replace function exec_sql(int) returns setof record as '
DECLARE
lookup_row record;
v_atttype text := ;
rec record;
BEGIN
FOR lookup_row IN SELECT * FROM att WHERE aid = $1
LOOP
v_atttype := lookup_row.atttype;
END LOOP;
FOR rec IN execute ''SELECT h.hid, h.context, d.val::'' || v_atttype ||
'' FROM hdr h, att a, det d '' ||
'' WHERE d.hid = h.hid and d.aid = a.aid and a.aid = '' || $1
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
' language plpgsql;
create or replace function write_sql(int) returns text as '
DECLARE
v_attname text := ;
v_atttype text := ;
v_result text;
lookup_row record;
BEGIN
FOR lookup_row IN SELECT * FROM att WHERE aid = $1
LOOP
v_attname := lookup_row.attname;
v_atttype := lookup_row.atttype;
END LOOP;
v_result := ''select hid, context, '' || v_attname ||
'' from exec_sql('' || $1 || '') as t(hid int, context
text, '' ||
v_attname || '' '' || v_atttype || '')'';
return v_result;
END;
' language plpgsql;
regression=# select write_sql(1);
write_sql
--
select hid, context, test_date from exec_sql(1) as t(hid int, context
text, test_date timestamp)
(1 row)
regression=# select hid, context, test_date from exec_sql(1) as t(hid
int, context text, test_date timestamp);
hid | context | test_date
-+-+-
1 | test1 | 2004-03-15 00:00:00
2 | test2 | 2004-03-16 00:00:00
(2 rows)
regression=# select write_sql(2);
write_sql
-
select hid, context, height from exec_sql(2) as t(hid int, context
text, height float8)
(1 row)
regression=# select hid, context, height from exec_sql(2) as t(hid int,
context text, height float8);
hid | context | height
-+-+-
1 | test1 | 3.14159
2 | test2 |2.34
(2 rows)
regression=# select write_sql(3);
write_sql
---
select hid, context, width from exec_sql(3) as t(hid int, context
text, width float8)
(1 row)
regression=# select hid, context, width from exec_sql(3) as t(hid int,
context text, width float8);
hid | context | width
-+-+---
1 | test1 | 2.8
2 | test2 | 3.28
(2 rows)
regression=# select write_sql(4);
write_sql
-
select hid, context, color from exec_sql(4) as t(hid int, context
text, color text)
(1 row)
regression=#
Re: [SQL] Trouble with composite types
Yasir Malik wrote: [attempts to use a composite type as a field data type of another composite type] When I enter that into the command prompt, I the following message: ERROR: Attribute street has composite type street_type Why is it giving me error message for something I know is true? Also, how do I add a member function to a type? What you are trying to do is currently unsupported. There have been multiple discussions in the past on this -- try searching the archives for more info. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] plpgsql function returning SETOF RECORD Question
[EMAIL PROTECTED] wrote: When I run the function below I recieve an error message saying that column definitions need to be specified for a function returing a type RECORD. I was under the impression that the FOR row IN SELECT... loop would assign a column structure to the RECORD type. Am I wrong about this? CREATE FUNCTION test() RETURNS SETOF RECORD AS ' DECLARE row RECORD; BEGIN FOR row IN SELECT * FROM dates LOOP RETURN NEXT row; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; Please look at the docs: http://www.postgresql.org/docs/current/static/sql-select.html Specifically, the latter half of this paragraph: Function calls can appear in the FROM clause. (This is especially useful for functions that return result sets, but any function can be used.) This acts as though its output were created as a temporary table for the duration of this single SELECT command. An alias may also be used. If an alias is written, a column alias list can also be written to provide substitute names for one or more attributes of the function's composite return type. If the function has been defined as returning the record data type, then an alias or the key word AS must be present, followed by a column definition list in the form ( column_name data_type [, ... ] ). The column definition list must match the actual number and types of columns returned by the function. and the example further down the page: CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS ' SELECT * FROM distributors WHERE did = $1; ' LANGUAGE SQL; SELECT * FROM distributors_2(111) AS (f1 int, f2 text); f1 | f2 -+- 111 | Walt Disney HTH, Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Scalar in a range (but textual not numeric)
Richard Huxton wrote: That's not quite the same though, because it means I need to split ABCAA..ABDBB into ABCAA..ABCZZ and ABDAA..ABDZZ but it's close enough unless someone is feeling clever this evening. Would (a series of) partial indexes help? Joe ---(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] Last day of month
Greg Sabino Mullane wrote:
How to find the last sunday/mon/sat of any given month.
There is probably a smoother way to do it, but here is a
quick little function to do what you ask. Feed it a date
and a number, where 0 is Sunday, 1 is Monday, etc.
oops...forget my last reply...I was a bit too quick on the draw. Try
this instead:
regression=# select date_trunc('month', current_date + '1
month'::interval) - '1 day'::interval;
?column?
-
2004-02-29 00:00:00
(1 row)
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] crosstabs
Philippe Lang wrote: I need to do something similar to a cross tabulation, but without any aggregation. See the crosstab() function found in contrib/tablefunc Joe ---(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] Compiling pl/pgsql functions
Rodrigo Sakai wrote: I insist in my question, is there a way to compile the plpgsql codes or something like that no think about writting this postgres functions in C?? yes Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] arrays and polygons
Tom Lane wrote:
David [EMAIL PROTECTED] writes:
INSERT INTO species (location) VALUES ('{((432,89), (45,87), (89,87)),
((54,78), (34,98))};
I think you'd need to double-quote each polygon within the array
literal.
'{((432,89), (45,87), (89,87)), ...}'
The array parser doesn't think parens are special, so it's not going to
magically distinguish array commas from polygon commas for you.
BTW, if you are using 7.4, the ARRAY[] constructor syntax might be
easier to use.
FWIW, here's what it would look like in 7.4.x:
regression=# select ARRAY['((432,89), (45,87), (89,87))'::polygon,
'((432,89), (45,87), (89,87))'];
array
-
{((432,89),(45,87),(89,87)),((432,89),(45,87),(89,87))}
(1 row)
You need to explicitly cast at least the first polygon in order to get
an array of polygons (versus an array of text).
HTH,
Joe
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] array_lower /array_prepend doubt
Bruce Momjian wrote: Is this a TODO? Probably -- something like: Modify array literal representation to handle array index lower bound of other than one Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to retrieve N lines of a text field.
Chris Travers wrote:
This is a complex issue, and i am tryign to figure out how to use regular
expressions to resolve this issue. I need to retrieve the first N lines of
a text field. N would be assigned using a parameterized query, if possible.
How 'bout something like this:
CREATE OR REPLACE FUNCTION first_n_lines(text, int)
RETURNS setof text AS '
DECLARE
i int := 0;
oneline text;
BEGIN
LOOP
i := i + 1;
IF i $2 THEN
EXIT;
END IF;
SELECT INTO oneline split_part($1, ''\n'', i);
IF oneline = THEN
EXIT;
END IF;
RETURN NEXT oneline;
END LOOP;
RETURN;
END
' LANGUAGE 'plpgsql';
regression=# select * from first_n_lines('abc\ndef\nghi', 2);
first_n_lines
---
abc
def
(2 rows)
HTH,
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] array_lower /array_prepend doubt
Tom Lane wrote: Rajesh Kumar Mallah [EMAIL PROTECTED] writes: can anyone explain why SELECT array_lower(array_prepend(0, ARRAY[1,2,3]), 1); returns 0 not 1 Because array_prepend keeps the subscripts of the existing array elements the same. This was discussed during development of the code, but I don't see anything in the documentation that mentions it. It could perhaps be added to the Functions and Operators page for arrays, but it is mentioned here: http://www.postgresql.org/docs/current/static/arrays.html#AEN5183 When a single element is pushed on to the beginning of a one-dimensional array, the result is an array with a lower bound subscript equal to the right-hand operand's lower bound subscript, minus one. When a single element is pushed on to the end of a one-dimensional array, the result is an array retaining the lower bound of the left-hand operand. For example: SELECT array_dims(1 || ARRAY[2,3]); array_dims [0:2] (1 row) SELECT array_dims(ARRAY[1,2] || 3); array_dims [1:3] (1 row) Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] array_lower /array_prepend doubt
Tom Lane wrote:
The other point about pg_dump failing to correctly restore arrays with
nondefault lower bounds is a good one, though. We need to think about
how to fix that.
I'll put some thought into it, but note that it is hardly a new issue --
it's been possible to create an array with 1 lower bound since well
before 7.4:
regression=# select version();
version
---
PostgreSQL 7.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2
20030222 (Red Hat Linux 3.2.2-5)
(1 row)
regression=# create table a(f1 int, f2 int[]);
CREATE TABLE
regression=# insert into a values (1,'{1,2}');
INSERT 565511 1
regression=# update a set f2[0] = 0 where f1 = 1;
UPDATE 1
regression=# select array_dims(f2) from a;
array_dims
[0:2]
(1 row)
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] array_lower /array_prepend doubt
Tom Lane wrote: Of course; I suppose this bug goes back to Berkeley days. We just hadn't recognized it before (or at least I hadn't). Neither had I. But the changes in 7.4 probably make it more likely people will bump into this as a problem. Without looking to confirm, I believe SQL99 defines an array as always having a lower bound of 1, making our behavior an extension to the standard. We may need another extension to the array literal syntax in order to deal with this. I'll report back after I've had some time to study it. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Fetching a single column from a record returning function
Kumar wrote: select * from .fn_email(1) as (email_folder_id int4,email_folder_name varchar,descrip varchar,msgcount int8,unreadcount int8,size int8); Is it possible to fetch only one column (the 'msgcount') from the function. Because I am interested in SUM(msgcount). Please shed some light. What's wrong with: select msgcount from fn_email(1) as (email_folder_id int4,email_folder_name varchar,descrip varchar,msgcount int8,unreadcount int8,size int8); ? Try showing us more detail about what you want to do and why it isn't currently working. Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Problem with plpgsql function
Chris Bowlby wrote:
select get_account_info('test.com');
I get this error:
ERROR: set-valued function called in context that cannot accept a set
This is the classic SRF error -- you need to use an SRF like a
relation in the FROM clause, so do this instead:
select * FROM get_account_info('test.com');
HTH,
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Seq Scans when index expected to be used
jasiek wrote: On Sat, 29 Nov 2003 08:49:24 -0800 (PST), ow wrote explain select b, c, a from test group by b, c, a having count(*) 1 Why would you expect this to use an index scan when it needs to read the entire table? If you read the whole table (or even a significant fraction of it), a seq scan is faster. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Seq Scans when index expected to be used
ow wrote: My impression was that the index I_bca covers the query, hence there should not be a need to go to the table itself. Why would it? Postgres always has to go to the table. The ability to read data directly from indexes (ala other RDBMSs) has been discussed, but not implemented. IIRC it's a hard problem due to the way Postgres does MVCC. Check the archives. explain analyze vs explain. Normally, would've used explain analyze but in this case it's taking way too long so I used explain. I can understand that, but most people will ask for explain analyze anyway ;-) Joe ---(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] Equivalent of Reverse() functions
Kumar wrote:
I am migrating an SQL Server 2000 database to Postgres 7.3.4 running
on RH Linux 7.2. While migrating I encounter SQL Server REVERSE( )
function, seems no such functions at Postgres.
Is there a equivalent function available at Postgres? Please shed
some light
How about:
create or replace function reverse(text) returns text as '
return reverse($_[0]);
' language plperl;
regression=# select reverse('abcdef');
reverse
-
fedcba
(1 row)
Joe
---(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] Scaler forms as function arguments
Andreas Tille wrote: test=# select PLpgSQLDepartmentSalaries() ; This should be: regression=# select * from PLpgSQLDepartmentSalaries(); departmentid | totalsalary --+- 1 | 8 2 | 6 (2 rows) HTH, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Query Problem
Abdul Wahab Dahalan wrote:
If I've a table like below.
kk kjpngkvote
01 02 c 10
01 02 b 5
How do I make a query so that I can get a result
like this?
kk kjpngkvote
01 02c,b 15
create or replace function accum_text(text, text) returns text as
'select case when $1 = then $2 else $1 || '','' || $2 end' language
sql;
CREATE AGGREGATE concat(BASETYPE = text, SFUNC = accum_text, STYPE =
text, INITCOND = '');
create table t(kk text, kj text, pngk text, vote int);
insert into t values('01','02','c',10);
insert into t values('01','02','b',5);
regression=# select kk, kj, concat(pngk), sum(vote) from t group by kk, kj;
kk | kj | concat | sum
+++-
01 | 02 | c,b| 15
(1 row)
HTH,
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] connectby
BenLaKnet wrote: I use postgresql 7.2.3 How can I use connectby ?? Must I install files ? or packages ? or it is recommanded to upgrade dataserver ? You need to upgrade. Either install 7.3.4 or wait a few weeks and install 7.4 when it is released. Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Crosstab question
Adam Witney wrote: The missing values seemed to have been ignored and so the data is being shifted to the left and so put in the wrong columns. Am I using this function correctly? What is supposed to happen with missing values? Yeah, that's a limitation of the version of crosstab distributed with 7.3. And (believe it or not) I've actually found cases where that behavior is useful. There is another version of crosstab (hashed crosstab) that treats missing values the way you expect. You can grab a copy backported for 7.3 here: http://www.joeconway.com/ You want the one that says: contrib/tablefunc backported for PostgreSQL 7.3.x -- sync'd with CVS HEAD 02-Oct-2003 HTH, Joe ---(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] Oracle 'connect by prior' now eaiser in 7.3?
Thomas Wegner wrote:
SELECT ID_WINUSER FROM connectby('WINUSER', 'ID_WINUSER',
'REF_ID_WINUSER', 4, 0, '~') AS t(ID_WINUSER integer)
and get this error:
ERROR: Query-specified return tuple not valid for Connectby: wrong number
of columns
Please see the documentation (README.tablefunc). You need to properly
specify the column definitions in the FROM clause, i.e. (untested):
SELECT ID_WINUSER FROM
connectby('WINUSER', 'ID_WINUSER','REF_ID_WINUSER', 4, 0, '~')
AS t(ID_WINUSER integer,
REF_ID_WINUSER integer,
level integer,
branch text);
HTH,
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Mystery function error
Tom Lane wrote:
Josh Berkus [EMAIL PROTECTED] writes:
Also, this will run faster if you do it as a SQL function:
CREATE FUNCTION locate ( text, text ) RETURNS INT AS '
SELECT POSITION($2, $1);
' LANGUAGE SQL IMMUTABLE STRICT;
This is definitely the solution I'd recommend for 7.4 (because 7.4 would
inline the SQL function definition, resulting in zero runtime overhead).
In 7.3 I suspect the plpgsql version might be a tad faster, or anyway
comparable. Has anyone done any head-to-head performance comparisons
on such simple functions?
I did a quick check last night on 7.3.4 and found that plpgsql was faster:
CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS '
-- search for the position of $2 in $1
declare
srcstr alias for $1;
searchstr alias for $2;
begin
return position(searchstr in srcstr);
end;
' LANGUAGE 'plpgsql' IMMUTABLE;
regression=# explain analyze select locate('abc','b');
QUERY PLAN
--
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01
rows=1 loops=1)
Total runtime: 0.03 msec
(2 rows)
DROP FUNCTION public.locate(bpchar, bpchar);
CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS '
select position($2 in $1)
' LANGUAGE 'sql';
regression=# explain analyze select locate('abc','b');
QUERY PLAN
--
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.24..0.24
rows=1 loops=1)
Total runtime: 0.26 msec
(2 rows)
On 7.4 (different hardware), I get this:
plpgsql
regression=# explain analyze select locate('abc','b');
QUERY PLAN
--
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.00..0.00
rows=1 loops=1)
Total runtime: 0.05 msec
(2 rows)
sql
regression=# explain analyze select locate('abc','b');
QUERY PLAN
--
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01
rows=1 loops=1)
Total runtime: 0.03 msec
(2 rows)
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Mystery function error
Richard Sydney-Smith wrote: CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS ' -- search for the position of $2 in $1 declare srcstr alias for $1; searchstr alias for $2; begin return position(searchstr in srcstr); ' LANGUAGE 'plpgsql' VOLATILE; You are missing the end keyword in there. Also, I'd think this function is IMMUTABLE not VOLATILE. CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS ' -- search for the position of $2 in $1 declare srcstr alias for $1; searchstr alias for $2; begin return position(searchstr in srcstr); end; ' LANGUAGE 'plpgsql' IMMUTABLE; This could also be done as: CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS ' select position($2 in $1) ' LANGUAGE 'sql'; HTH, Joe ---(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] Oracle 'connect by prior' now eaiser in 7.3?
Merrall, Graeme wrote: Am I right in thinking that recursive procedures and procs returning row sets would allow us to better emulate this behaviour? As anyone looked at it yet? See connectby() in contrib/tablefunc. Someone was working on SQL99 recursive queries but it didn't get done for 7.4 -- perhaps it will be in 7.5. In the meantime, connectby() is in 7.3 and might work for you. HTH, Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] How to return a record set from function.
Kumar wrote: Create table t1 (c1 int, c2 varchar, c3 varchar); Create or Replace function sel_t1 () returns setof records as ' select c1, c2, c3 from t1; ' Language SQL; It was fine and created a function. while i execute it as select sel_t1; I got the following error. ERROR: Cannot display a value of type RECORD I see three problems. 1) you need parenthesis on the function call, i.e. sel_t1() as compared with sel_t1 2) when returning setof record, the sel_t1() must be in the FROM clause of the statement 3) when the function is declared as returning record as compared to a named complex type such as t1, you need to include a column definition list in the SQL statement So, putting it all together, try something like this: SELECT f1, f2, f3 FROM sel_t1() AS (f1 int, f2 varchar, f3 varchar); See: http://www.postgresql.org/docs/view.php?version=7.3idoc=0file=xfunc-tablefunctions.html and http://techdocs.postgresql.org/guides/SetReturningFunctions HTH, Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] length of array
Chris Faulkner wrote: Is there a function in postgres to return the length of an array field ? I have seen array_dims(array) but this returns a character value. Ideally, I'd like something numeric returned. Afraid not in 7.3.x or earlier. In 7.3.x (but not earlier) you *can* do this (for a one-dimensional array at least): SELECT replace(split_part(array_dims(array_fld),':',1),'[','')::int as low FROM tbl; SELECT replace(split_part(array_dims(array_fld),':',2),']','')::int as high FROM tbl; In 7.4 (now in beta) there are two new functions, array_lower() and array_upper() that do what you're looking for: regression=# select array_lower(array_fld, 1) from tbl; array_lower - 1 (1 row) regression=# select array_upper(array_fld, 1) from tbl; array_upper - 2 (1 row) See the following links for more on 7.4's array support: http://developer.postgresql.org/docs/postgres/arrays.html http://developer.postgresql.org/docs/postgres/functions-array.html http://developer.postgresql.org/docs/postgres/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS HTH, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SELECT IN Still Broken in 7.4b
Mike Winter wrote: On Wed, 20 Aug 2003, Rod Taylor wrote: Ensure your IN list is unique. You might find better times by through an indexed temp table. That is what I ended up doing, but it's not a very elegant solution. MySQL does queries of this type orders of magnitudes faster than Postgres on large value lists, although I have no specific algorithmic solutions to offer for how to make it faster. I don't believe making the IN lists unique has any affect on performance. I have no idea whether it will be better or worse performance, but in 7.4 you could do: select blah from foo where id = any (ARRAY[list_of_literals]); Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Table conversion query...
George McQuade wrote: date tran glamt 08/20/03 1001 3010 -30.00 08/20/03 1001 1030 -300.00 08/20/03 1001 1060 +330.00 08/20/03 1002 ...next transaction ... and I need to convert to: date glcr gldb amt 08/20/03 1060 3010 30.00 08/20/03 1060 1030 300.00 in other words, the negative gl's go into gldb and they make up the total for the positive gl. is there a way to accomplish this in postgresql? or should I implement it inside the java app? There's no simple way to do this in Postgres. You could do it with a PL/pgSQL table function, or for better performance a C function. There are a couple of questions yet to be answered though: 1) Can there ever be more than one credit account, e.g. -30, -300, +150, +180? 2) What happens if sum(neg values) != sum(pos values)? Throw an error? Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Table conversion query...
George McQuade wrote:
Interesting, my C is gone a long time ago. Would the
table function be fairly complex for someone who's
never done one?
I'm tempted by the java option, but initial jdbc speed
tests don't look very promising (the avg file has
1/2 million records).
Well this is a fairly complex problem regardless of the language. You
need to be able to accumulate output rows for an arbitrary number of
different debit gls, flush them out whenever the transaction id changes,
and be sure that they reconcile with the credit.
I'm actually not sure you can do this in plpgsql in Postgres 7.3.x -- in
7.4 you could use arrays to accumulate the debit data. Here is a lightly
tested (no warranty, might not work correctly, use at your own risk etc,
etc ;-) ) plpgsql function which seems to work as you want it. Consider
it a starting point, but only if you can use 7.4beta:
create table gl (transdate date, tran int, gl int, amt numeric(9,2));
insert into gl values('08/20/03',1001,3010,-30.00);
insert into gl values('08/20/03',1001,1030,-300.00);
insert into gl values('08/20/03',1001,1060,330.00);
insert into gl values('08/21/03',1002,3010,-30.00);
insert into gl values('08/21/03',1002,1030,-200.00);
insert into gl values('08/21/03',1002,3010,-100.00);
insert into gl values('08/21/03',1002,1060,330.00);
create type reconcile_type as (transdate date, glcr int, gldb int, amt
numeric(9,2));
create or replace function reconcile(text) returns setof reconcile_type as '
declare
v_crit alias for $1;
v_sql text;
v_last_transdate date;
v_last_tran int := 0;
v_last_glcr int := 0;
v_last_glcr_amt numeric(9,2) := 0;
v_last_gldb int[] := ''{}'';
v_last_gldb_amt numeric(9,2)[] := ''{}'';
v_sum_debit numeric(9,2) := 0;
v_glcr_found bool := false;
rec record;
result reconcile_type%rowtype;
i int;
ub int;
begin
if v_crit is not null then
v_sql := ''select transdate, tran, gl, sum(amt) as amt from gl where ''
|| v_crit ||
'' group by transdate, tran, gl order by 2,4 desc,1,3'';
else
v_sql := ''select transdate, tran, gl, sum(amt) as amt from gl '' ||
''group by transdate, tran, gl order by 2,4 desc,1,3'';
end if;
for rec in execute v_sql loop
if rec.tran != v_last_tran then
-- starting a new tran
if v_glcr_found = true then
-- time to flush rows, but did we reconcile
if v_sum_debit != -(v_last_glcr_amt) then
RAISE EXCEPTION ''credits and debits do not reconcile'';
end if;
-- flush accumulated results, looping over gldb arrays
ub := array_upper(v_last_gldb, 1);
for i in 1..ub loop
result.transdate := v_last_transdate;
result.glcr := v_last_glcr;
result.gldb := v_last_gldb[i];
result.amt := v_last_gldb_amt[i];
return next result;
end loop;
end if;
-- first pass for this tran -- it better be a credit
if rec.amt 0 then
v_glcr_found := true;
else
RAISE EXCEPTION ''no credit found for transaction: %'', rec.tran;
end if;
v_last_tran := rec.tran;
v_last_transdate := rec.transdate;
v_last_glcr := rec.gl;
v_last_glcr_amt := rec.amt;
v_last_gldb := ''{}'';
v_last_gldb_amt := ''{}'';
v_sum_debit := 0;
else
-- not a new tran
if rec.amt 0 then
-- if we have already visited the credit, and we see another,
cry foul
RAISE EXCEPTION ''Two credits found for transaction: %'', rec.tran;
else
-- otherwise accumulate the debit
v_last_gldb := v_last_gldb || rec.gl;
v_last_gldb_amt := v_last_gldb_amt || rec.amt;
v_sum_debit := v_sum_debit + rec.amt;
end if;
end if;
end loop;
-- need this to get the last (or only) trans
if v_glcr_found = true then
-- time to flush rows, but did we reconcile
if v_sum_debit != -(v_last_glcr_amt) then
RAISE EXCEPTION ''credits and debits do not reconcile'';
end if;
-- flush accumulated results, looping over gldb arrays
ub := array_upper(v_last_gldb, 1);
for i in 1..ub loop
result.transdate := v_last_transdate;
result.glcr := v_last_glcr;
result.gldb := v_last_gldb[i];
result.amt := v_last_gldb_amt[i];
return next result;
end loop;
end if;
return;
end;
' language plpgsql;
regression=# select * from reconcile(null);
transdate | glcr | gldb | amt
+--+--+-
2003-08-20 | 1060 | 3010 | -30.00
2003-08-20 | 1060 | 1030 | -300.00
2003-08-21 | 1060 | 3010 | -130.00
2003-08-21 | 1060 | 1030 | -200.00
(4 rows)
You could do similar a C function in 7.3.x. I'm not sure how you'd write
this in 7.3.x plpgsql though :(
HTH,
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [PERFORM] EXTERNAL storage and substring on long strings
Scott Cain wrote: Oh, and I forgot to mention: it's highly compressed (bzip2 -9) and is 109M. Thanks. I'll grab a copy from home later today and see if I can find some time to poke at it. Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [PERFORM] EXTERNAL storage and substring on long strings
Scott Cain wrote: On Mon, 2003-08-04 at 11:53, Tom Lane wrote: I find it really, really hard to believe that a crude reimplementation in plpgsql of the TOAST concept could beat the built-in implementation at all, let alone beat it by two orders of magnitude. Either there's something unrealistic about your testing of the dna_string function, or your original tests are not causing TOAST to be invoked in the expected way, or there's a bug we need to fix. I'd really like to see some profiling of the poor-performing external-storage case, so we can figure out what's going on. I was really hoping for a Good job and glad to hear it from you :-) I don't think there is anything unrealistic about my function or its testing, as it is very much along the lines of the types of things we do now. I will really try to do some profiling this week to help figure out what is going on. Is there a sample table schema and dataset available (external-storage case) that we can play with? Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] function returning setof performance question
Mark Bronnimann wrote: I was hoping to eliminate the parse call on the view because I was doing the where clause on the view instead of putting the where in the view. In all, I was hoping to keep a single view called from multiple functions with different where clauses. Yep... I shoulda known better... It sounds like you're using a sql function, not a plpgsql function (although I don't think you said either way). If you write the function in plpgsql it will get parsed and cached on the first call in a particular backend session, which *might* give you improved performance on subsequent calls, if there are any; are you using persistent connections? Alternatively, it might work to use a prepared query. Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] How access to array component
Cristian Cappo A. wrote: Tried, but... select (foo(10::int2,20::int2))[1]; ERROR: parser: parse error at or near [ at character 32 I'm using the version 7.3.3 Sorry, it works on 7.4devel, so I thought it might on 7.3 as well. In any case, this works on 7.3.3: test=# select f1[1] from (select foo(10::int2, 20::int2) as f1) as ss; f1 1 (1 row) HTH, Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] How access to array component
Cristian Cappo wrote:
select __function(10::int2, 20::int2)[1]
^^^ parsing error.
Try:
create or replace function foo(int2, int2 ) returns _varchar as '
select ''{1,2}''::_varchar
' language 'sql';
regression=# select (foo(10::int2, 20::int2))[1];
foo
-
1
(1 row)
HTH,
Joe
---(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 to arrays
Mike Rylander wrote:
I have a rather odd table structure that I would like to simplify to be a view
(for some definition of simplify). The current idea I have is to shovel
values from multiple rows in one table into an array in the view. The tables
look something like this:
snip
Is anything like this possible? I know this may not be good form, but
unfortunately (or perhaps fortunately, since it means I have a job) there are
business reasons for this, supporting old apps and such.
Not possible in current releases, but it will be in 7.4 (about to start
beta). It looks like this:
create table person (id integer, name varchar);
insert into person values(1,'Bob');
insert into person values(2,'Sue');
create table stuff (person_id integer, stuff_name text);
insert into stuff values(1,'chair');
insert into stuff values(1,'couch');
insert into stuff values(1,'lamp');
insert into stuff values(2,'table');
insert into stuff values(2,'shirt');
create or replace view person_with_stuff as select p.id as id, p.name as
name, ARRAY(select s.stuff_name from stuff s where s.person_id = p.id)
as stuff from person p;
regression=# select * from person_with_stuff;
id | name | stuff
+--+
1 | Bob | {chair,couch,lamp}
2 | Sue | {table,shirt}
(2 rows)
HTH,
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] casting to arrays
Mike Rylander wrote: Thank you! This is great news. Is there a projected release date for 7.4? Not exactly an officially projected date, but in the past IIRC beta/RC has lasted 2 to 3 months, so I'd start looking for a 7.4 release in October. Also, is there a published roadmap, or should I just get on the developers list? The closest thing is the TODO list: http://developer.postgresql.org/todo.php But if you want to closely monitor the work actually getting done, subscribe to the HACKERS list. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Recursive request ...
BenLaKnet wrote: I see connect by in Oracle ??? is there an equivalent in PostgreSQL or not ?? Someone is working on the SQL99 equivalent, but it isn't done yet. Perhaps for 7.5. Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Functional Indexes
David Olbersen wrote:
Now the question: is there a single index I can create that will be
used when my WHERE clause contains either urlhost or urltld? I could
create two functional indexes, but that seems a bit silly to me.
I can't think of how to do only one index in 7.3.x and earlier, but
FWIW, this works in 7.4devel (which should be in beta next Monday):
create or replace function tld(text) returns text as '
select split_part(split_part(substr($1,8,length($1)),''/'',1),''.'',3)
' language 'sql' STRICT IMMUTABLE;
regression=# select tld('http://www.foobar.com/really/long/path/to/a/file');
tld
-
com
(1 row)
create or replace function sld(text) returns text as '
select split_part(split_part(substr($1,8,length($1)),''/'',1),''.'',2)
' language 'sql' STRICT IMMUTABLE;
regression=# select sld('http://www.foobar.com/really/long/path/to/a/file');
sld
foobar
(1 row)
create table urls(f1 text);
insert into urls values('http://www.foobar.com/really/long/path/to/a/file');
create index urls_idx1 on urls(tld(f1),sld(f1));
-- just to see index usage on toy table
set enable_seqscan to off;
regression=# explain analyze select * from urls where tld(f1) = 'com';
QUERY PLAN
--
Index Scan using urls_idx1 on urls (cost=0.00..4.69 rows=1 width=32)
(actual time=0.07..0.07 rows=1 loops=1)
Index Cond: (split_part(split_part(substr(f1, 8, length(f1)),
'/'::text, 1), '.'::text, 3) = 'com'::text)
Total runtime: 0.18 msec
(3 rows)
regression=# explain analyze select * from urls where tld(f1) = 'com'
and sld(f1) = 'foobar';
QUERY PLAN
-
Index Scan using urls_idx1 on urls (cost=0.00..4.70 rows=1 width=32)
(actual time=0.08..0.09 rows=1 loops=1)
Index Cond: ((split_part(split_part(substr(f1, 8, length(f1)),
'/'::text, 1), '.'::text, 3) = 'com'::text) AND
(split_part(split_part(substr(f1, 8, length(f1)), '/'::text, 1),
'.'::text, 2) = 'foobar'::text))
Total runtime: 0.21 msec
(3 rows)
Joe
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Replacing a simple nested query?
Steve Wampler wrote: I've got a simple nested query: select * from attributes where id in (select id from attributes where (name='obsid') and (value='oid00066')); that performs abysmally. I've heard this described as the 'classic WHERE IN' problem. I may be missing something, but why can't you just do: select * from attributes where name='obsid' and value='oid00066'; ? Joe ---(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] Recursive request ...
Benoît Bournon wrote: I have to make a function that returns a tree with title and link of a table. Recursively, a information depends on a parent information. It is to organise a menu with parent dependance. How is it possible and faster ? in C ? pl/pgsql or other ? On 7.3 and later: see contrib/tablefunc. The function is called connectby(). HTH, Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Setuid functions
Luis Sousa wrote:
How can I set a function as setuid ?
I take a look at the documetation, on Reference Manual and the only
reference I saw to it was on SET SESSION AUTHORIZATION.
See:
http://www.postgresql.org/docs/view.php?version=7.3idoc=0file=sql-createfunction.html
CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] )
RETURNS rettype
{ LANGUAGE langname
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER
| AS 'definition'
| AS 'obj_file', 'link_symbol'
} ...
[ WITH ( attribute [, ...] ) ]
[EXTERNAL] SECURITY INVOKER
[EXTERNAL] SECURITY DEFINER
SECURITY INVOKER indicates that the function is to be executed with
the privileges of the user that calls it. That is the default. SECURITY
DEFINER specifies that the function is to be executed with the
privileges of the user that created it.
The key word EXTERNAL is present for SQL compatibility but is
optional since, unlike in SQL, this feature does not only apply to
external functions.
HTH,
Joe
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] LEAST and GREATEST functions?
Greg Stark wrote:
SELECT greatest(a,b) FROM bar
would return one tuple for every record in the table with a single value
representing the greater of bar.a and bar.b.
You could define your own functions to do this but it would be tiresome to
define one for every datatype.
In 7.4devel (just starting beta) you can do this:
create or replace function greatest(anyelement, anyelement) returns
anyelement as 'select case when $1 $2 then $1 else $2 end' language 'sql';
regression=# select greatest(1, 2);
greatest
--
2
(1 row)
regression=# select greatest('b'::text, 'a');
greatest
--
b
(1 row)
regression=# select greatest(now(), 'yesterday');
greatest
---
2003-07-01 13:21:56.506106-07
(1 row)
The cast to text is needed because 'a' and 'b' are really typed as
unknown, and with polymorphic functions, you need a well defined data type.
So if you had a table:
create table g(f1 text, f2 text);
insert into g values ('a','b');
insert into g values ('c','b');
regression=# select greatest(f1, f2) from g;
greatest
--
b
c
(2 rows)
Doesn't help for 7.3.x, but at least you know help is on the way ;-)
Of course, you could always just use the case statement.
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] LEAST and GREATEST functions?
Joe Conway wrote: In 7.4devel (just starting beta) you can do this: Actually to correct myself, we just started feature freeze for 7.4, with beta planned to start on or about July 15th. Sorry for any confusion caused. Joe ---(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] LEAST and GREATEST functions?
Josh Berkus wrote: create or replace function greatest(anyelement, anyelement) returns anyelement as 'select case when $1 $2 then $1 else $2 end' language 'sql'; Way cool. I'd have to imagine that it would blow up if you did this, though: select greatest ( 512, now() ); With an Operator is not defined error, hey? It errors out with a type mismatch error: regression=# select greatest (512, now()); ERROR: Function greatest(integer, timestamp with time zone) does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts Of course none of this is documented yet (because I still owe the documentation ;-), but that can be done during feature freeze/beta), but the concept of the anyelement data type is that, although it can mean literally any data type, any arguments (or return type) so defined have to match each other at function call time. So with: greatest(anyelement, anyelement) returns anyelement when it gets called, the two arguments *must* be the same data type, and the function will return the same type. Any arguments declared with a specific datatype (say integer) don't participate in the runtime resolution of the polymorphic arguments. Similarly there is an anyarray data type that is constrained at runtime to be an array of anything that was defined as anyelement; e.g.: create or replace function myelement(anyarray, int) returns anyelement as 'select $1[$2]' language 'sql'; regression=# select myelement(array[11,22,33,44,55], 2); myelement --- 22 (1 row) Joe ---(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] passing a record as a function argument in pl/pgsql
Alon Noy wrote: From what I tried it is possible to create such a function but it is not possible to call it ?! Can anyone provide an example? create table foo (f1 int, f2 text); insert into foo values(1,'a'); insert into foo values(2,'b'); insert into foo values(3,'c'); create or replace function get_foo(int) returns foo as 'select * from foo where f1 = $1' language 'sql'; create or replace function use_foo(foo) returns text as ' declare v_foo alias for $1; begin return v_foo.f2; end; ' language 'plpgsql'; regression=# select use_foo(get_foo(2)); use_foo - b (1 row) HTH, Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Catching DML exceptions in PL/pgSQL
Radu-Adrian Popescu wrote: . begin _res.code:=1; select id into iid from log where id=_id; if not found then begin _res.msg:=''insert''; *insert into log (log, data) values (_log, _data); if not found* then begin _res.msg:=_res.msg || '' error''; _res.code:=-1; end; end if; end; else begin . The thing is if _data (parameter) is null and table has a (data null) check, the insert would fail and abort the function before my if not found test. You could test for _data is null, and if so check attnotnull in pg_attribute. E.g. something like: declare iattnotnull bool [...] begin if _data is null then select into iattnotnull attnotnull from pg_catalog.pg_attribute where attrelid = 'log'::regclass and attname = 'data'; if iattnotnull then _res.code := -1; [...] Is there anything I can do to make sure the function always returns _res ? Something along the lines of Oracle's exception handling, or the @@error trick in mssql ? There is currently no way to catch the exception in PL/pgSQL, but maybe the above would work for you. HTH, Joe ---(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] Join on delimeter aggregate query
Michael A Nachbaur wrote: Source Target [EMAIL PROTECTED] [EMAIL PROTECTED],[EMAIL PROTECTED], It isn't clear from your description what you want (to me, anyway), but I'd guess something like this: regression=# select * from src2tgt; source| target -+-- [EMAIL PROTECTED] | [EMAIL PROTECTED] [EMAIL PROTECTED] | [EMAIL PROTECTED] [EMAIL PROTECTED] | [EMAIL PROTECTED] [EMAIL PROTECTED] | [EMAIL PROTECTED] (4 rows) create or replace function mylist(text,text) returns text as ' declare result text; begin if $1 = then result := $2; else result := $1 || '','' || $2; end if; return result; end; ' language 'plpgsql'; create aggregate tgtlist ( BASETYPE = text, SFUNC = mylist, STYPE = text, INITCOND = '' ); regression=# select source, tgtlist(target) from src2tgt group by source; source| tgtlist -+--- [EMAIL PROTECTED] | [EMAIL PROTECTED],[EMAIL PROTECTED] [EMAIL PROTECTED] | [EMAIL PROTECTED],[EMAIL PROTECTED] (2 rows) HTH, Joe ---(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] Using a RETURN NEXT
Mr Weinbach, Larry wrote: But at execution time I am getting thi error : WARNING: Error occurred while executing PL/pgSQL function word_case WARNING: line 5 at return next ERROR: Set-valued function called in context that cannot accept a set You didn't show the execution time SQL statement, but the error you are getting usually indicates you did something like: SELECT word_case(); but you should have done: (define the function to return setof record) SELECT * FROM word_case() AS (message text); or CREATE TYPE word_case_type AS (message text); (define the function to return setof word_case_type) SELECT * FROM word_case(); HTH, Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] generic return for functions
Danny Su wrote:
I am currently converting everything from SQL Server to PostgreSQL.
This is for an application that is going to support Oracle, SQL Server
and PostgreSQL at the same time. I have done a lot of the conversion
already but I am stuck on functions that returns parts of views or tables.
[...snip description of SQL Server 2000 table-valued UDFs...]
I know returns setof record and returns setof my_own_datatype work,
but I would like to know if there is a better way? Something that's like
returns setof record but without having to define all the columns when
I call the function? {i.e. without the need to do: select * from
myfunction() as (column1 type1, column2...);}
If there is such method? It will allow me to maintain the application
much easier and makes the conversion task much easier :)
Sorry -- the answer is no. But I don't think Oracle will support what
you want either.
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] plpgsql + dblink() question
Frankie wrote:
The case is when I call a function (from Server 1 at psql prompt) that will
call dblink to do some operation
on another server(it will take certain time), i.e. Server 2, and meanwhile I
just unplug the network cable to Server 2.
The consequence is that the function will never return except I plug the
cable into it again, moreover I cannot even cancel
the query and stop the postgresql server (have to 'kill -9'.)
dblink just uses libpq to handle the communication, so you can use the
connect_timeout connection parameter. It defaults to infinite if not
explicitly set. Something like this should set a 5 second timeout:
select * into tmp from dblink(''host=linux dbname=twins connect_timeout=5'',
''select mysleep();'') as (retval text);
See:
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/libpq-connect.html
HTH,
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Postgres MD5 Function
David Durst wrote: Is there anywhere I can get these in binary? Or is my only option to compile Postgres from source?? Depends on the type of binary. E.g. there is a postgresql-contrib-7.3.1-1PGDG.i386.rpm binary available here: ftp://ftp.us.postgresql.org/binary/v7.3.1/RPMS/redhat-7.3/ Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Postgres MD5 Function
Larry Rosenman wrote:
--On Friday, January 31, 2003 01:34:42 -0800 David Durst
[EMAIL PROTECTED] wrote:
Does there exsist a MD5 Function I can call???
look at /contrib/pgcrypto in the source distribution.
Also worth noting is that 7.4 will have (and cvs HEAD has) a builtin md5 function:
regression=# select md5('Joe');
md5
--
3a368818b7341d48660e8dd6c5a77dbe
(1 row)
HTH,
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Could someone help me fix my array_list function?
Guy Fraser wrote:
This is what I want to do:
select attribute,array_list(values,1,sizeof(values)) as value from av_list;
Turn :
attr6 | {val3,val7,val4,val5}
Into :
attr6 | val3
attr6 | val7
attr6 | val4
attr6 | val5
You didn't mention the version of PostgreSQL. If you're using 7.3, good luck
;-). If you are using 7.3, the following works:
DROP TABLE mail_aliases;
CREATE TABLE mail_aliases(
a_mailbox text,
a_destination text[]
);
INSERT INTO mail_aliases VALUES ('alias1', '{dest1}');
INSERT INTO mail_aliases VALUES ('alias2', '{dest2,dest1}');
INSERT INTO mail_aliases VALUES ('alias3', '{dest3,dest4}');
INSERT INTO mail_aliases VALUES ('alias4', '{dest3,dest4,dest5}');
INSERT INTO mail_aliases VALUES ('alias5', '{dest6,dest7}');
INSERT INTO mail_aliases VALUES ('alias6', '{dest3,dest7,dest4,dest5}');
CREATE TYPE mail_aliases_list_type AS (a_mailbox text, a_destination_el text);
CREATE OR REPLACE FUNCTION mail_aliases_list() RETURNS SETOF
mail_aliases_list_type AS '
DECLARE
rec record;
retrec record;
low int;
high int;
BEGIN
FOR rec IN SELECT a_mailbox, a_destination FROM mail_aliases LOOP
SELECT INTO low
replace(split_part(array_dims(rec.a_destination),'':'',1),''['',)::int;
SELECT INTO high
replace(split_part(array_dims(rec.a_destination),'':'',2),'']'',)::int;
FOR i IN low..high LOOP
SELECT INTO retrec rec.a_mailbox, rec.a_destination[i];
RETURN NEXT retrec;
END LOOP;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';
regression=# SELECT a_mailbox, a_destination_el FROM mail_aliases_list();
a_mailbox | a_destination_el
---+--
alias1| dest1
alias2| dest2
alias2| dest1
alias3| dest3
alias3| dest4
alias4| dest3
alias4| dest4
alias4| dest5
alias5| dest6
alias5| dest7
alias6| dest3
alias6| dest7
alias6| dest4
alias6| dest5
(14 rows)
HTH,
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Could someone help me fix my array_list function?
Michiel Lange wrote:
Would the same work for pg_user and pg_group?
See:
http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=11378
With these groups:
regression=# select * from pg_group;
groname | grosysid |grolist
-+--+---
grp1| 100 | {100,101,102}
grp2| 101 | {100,102}
(2 rows)
Output looks like:
regression=# select * from groupview;
grosysid | groname | usesysid | usename
--+-+--+-
100 | grp1| 100 | user1
100 | grp1| 101 | user2
100 | grp1| 102 | user3
101 | grp2| 100 | user1
101 | grp2| 102 | user3
(5 rows)
Joe
---(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] DB2 to Postgresql conversion help.
John Pauley wrote: All, Any suggestions on a Postgresql equivalent to the following DB2 sql query: SELECT * FROM (SELECT ID,NAME,CITY,STATE,PROGRAM,CUST_NAME,CUST_ID,ROWNUMBER() OVER (ORDER BY ID ASC) AS RN FROM MERCHANT_BROWSER_VIEW WHERE CUST_ID = 'A51B8CA2' ) AS RESULT WHERE RN BETWEEN 1 AND 20 I'm not familiar with DB2, but I'd guess something like: SELECT ID,NAME,CITY,STATE,PROGRAM,CUST_NAME,CUST_ID FROM MERCHANT_BROWSER_VIEW WHERE CUST_ID = 'A51B8CA2' ORDER BY ID LIMIT 20; HTH, Joe ---(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] SQL syntax for concating values in different rows together
Elizabeth O'Neill's Office Mail wrote: I have two tables in my database a complaint table and a resolution table. One complaint may have several resolutions. I am trying to build a report that will give me the complaint details and all the resolution descriptions for a complaint in one text area/row (concated together). At the moment it is repeating the complaint details for each resolution. As someone else mentioned I think, you can use a plpgsql function. Here is a contrived example: create table complaint(cid int, descr text); insert into complaint values(1,'my #1 complaint'); insert into complaint values(2,'my #2 complaint'); create table resolution(rid int, cid int, res text); insert into resolution values (1,1,'fixed it'); insert into resolution values (2,1,'really fixed it!'); insert into resolution values (3,2,'pbkbac again'); create or replace function my_concat(int) returns text as' declare ret text; comp text; rec record; cntr int = 0; begin select into comp descr from complaint where cid = $1; ret := ''Comp = '' || comp; for rec in select res from resolution where cid = $1 loop cntr := cntr + 1; ret := ret || '': Res# '' || cntr::text || '' = '' || rec.res; end loop; return ret; end; ' language 'plpgsql'; regression=# select my_concat(cid) from complaint; my_concat -- Comp = my #1 complaint: Res# 1 = fixed it: Res# 2 = really fixed it! Comp = my #2 complaint: Res# 1 = pbkbac again (2 rows) In the past I think I remember someone trying to solve this kind of problem with a custom aggregate, but the plpgsql approach is probably simpler. HTH, Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Trees: maintaining pathnames
Dan Langille wrote:
Given that I'm considering adding a new field path_name to the tree,
I can't see the ltree package will give me anything more than I can
get from like. My main reason for adding path_name was doing queries
such as:
select * from tree where path_name like '/path/to/parent/%'
which will return me all the descendants of a give node (in this case
'/path/to/parent/'.[2]
FWIW, you could also do this with connectby() in contrib/tablefunc (new in
7.3; see the README for syntax details):
test=# SELECT t.* FROM connectby('tree', 'id', 'parent_id', '1', 0, '~') AS
c(id int, parent_id int, level int, branch text), tree t WHERE t.id = c.id;
id | parent_id |name
+---+
1 | | Top
2 | 1 | Science
3 | 2 | Astronomy
4 | 3 | Astrophysics
5 | 3 | Cosmology
6 | 1 | Hobbies
7 | 6 | Amateurs_Astronomy
8 | 1 | Collections
9 | 8 | Pictures
10 | 9 | Astronomy
11 |10 | Stars
12 |10 | Galaxies
13 |10 | Astronauts
(13 rows)
test=# SELECT t.* FROM connectby('tree', 'id', 'parent_id', '6', 0, '~') AS
c(id int, parent_id int, level int, branch text), tree t WHERE t.id = c.id;
id | parent_id |name
+---+
6 | 1 | Hobbies
7 | 6 | Amateurs_Astronomy
(2 rows)
test=# SELECT t.* FROM connectby('tree', 'id', 'parent_id', '8', 0, '~') AS
c(id int, parent_id int, level int, branch text), tree t WHERE t.id = c.id;
id | parent_id |name
+---+-
8 | 1 | Collections
9 | 8 | Pictures
10 | 9 | Astronomy
11 |10 | Stars
12 |10 | Galaxies
13 |10 | Astronauts
You could also do:
CREATE OR REPLACE FUNCTION node_id(text) returns int as 'select id from tree
where name = $1' language 'sql';
test=# SELECT t.* FROM connectby('tree', 'id', 'parent_id',
node_id('Science'), 0) AS c(id int, parent_id int, level int), tree t WHERE
t.id = c.id;
id | parent_id | name
+---+--
2 | 1 | Science
3 | 2 | Astronomy
4 | 3 | Astrophysics
5 | 3 | Cosmology
(4 rows)
I have discussed [offlist] the option of using a secondary table to
store the pathname (i.e. a cach table) which would be updated using a
loop in the tigger instead of using cascading triggers. I would
prefer to keep the pathname in the same table.
In my application, I have about 120,000 nodes in the tree. I am
using PL/pgSQL quite a lot. Perhaps moving the triggers to C at a
later date may provide a speed increase if the tree expands
considerably.
I've tested connectby() on a table with about 220,000 nodes. It is pretty fast
(about 1 sec to return a branch with 3500 nodes), and is entirely dynamic
(requires no triggers).
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Proposal of hierarchical queries, a la Oracle
Evgen Potemkin wrote: Joe, i've made it already,as send first 'Proposal ...' message, but found a small bug. within nearest days i'll fix it, and post the patch to pgsql-patches. Please note that there was no patch attached to your initial proposal (assuming this is the message you are referring to): http://archives.postgresql.org/pgsql-sql/2002-11/msg00226.php -- I think that's why people proposed so many alternatives to you. In any case, we'll look forward to your patch! Regards, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Proposal of hierarchical queries, a la Oracle
Josh Berkus wrote: Evgren, I want to propose the patch for adding the hierarchical queries posibility. It allows to construct queries a la Oracle for ex: SELECT a,b FROM t CONNECT BY a PRIOR b START WITH cond; You'll be thrilled to know that this has already been implemented in PostgreSQL 7.3, as a /contrib module, by Joe Conway. Download 7.3b5 now if you can't wait; Joe would like more people to test his module, anyway. I have it on my personal TODO to approach this for 7.4, but I'll be happy to focus on other things if you'd like to take this on. The connectby() function in contrib/tablefunc could be a good starting point for you. Joe ---(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] execute a query in an other db
LR wrote: Thank you for your answer. Do you know the name of this package ? Thx Lilian see contrib/dblink. I'd highly recommend using 7.3 (finishing beta, soon to be a release candidate) if you can. It is much better than the one in 7.2 and the syntax has changed some. Joe ---(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] Multiple Databases
Josh Berkus wrote: Is it possible to create a view using tables from two different postgresql servers? No. It isn't possible with plain old SQL, but it is possible (albeit ugly) using contrib/dblink in PostgreSQL 7.2.x. See README.dblink for documentation and examples. In PostgreSQL 7.3 (in beta testing now) the syntax and usability of contrib/dblink is greatly improved. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Get A Tree from a table
Scott Yaung wrote:
I like to do something like this:(build a tree from relationship)
[snip]
How can i make it by sql , and sql functions
Thanks lot and regards.
There have been quite a few discussions of this topic in the past, so I would
suggest you search through the archives.
In 7.3 (currently in beta) you can use contrib/ltree or contrib/tablefunc.
Here's an example of using the connectby() function from contrib/tablefunc:
CREATE TABLE nodes(parentid varchar(20), parenttype varchar(20), childid
varchar(20), childtype varchar(20));
INSERT INTO nodes values('A1', 'A', 'B1', 'B');
INSERT INTO nodes values('A2', 'A', 'B2', 'B');
INSERT INTO nodes values('A1', 'A', 'B3', 'B');
INSERT INTO nodes values('B1', 'B', 'C1', 'C');
INSERT INTO nodes values('B1', 'B', 'C2', 'C');
INSERT INTO nodes values('C1', 'C', 'D1', 'D');
INSERT INTO nodes values('A1', 'A', 'B4', 'B');
INSERT INTO nodes values('B1', 'B', 'C5', 'C');
test=# SELECT * FROM connectby('nodes','childid','parentid','A1',0,'~') AS
t(childid varchar, parentid varchar, level int, branch text);
childid | parentid | level | branch
-+--+---+-
A1 | | 0 | A1
B1 | A1 | 1 | A1~B1
C1 | B1 | 2 | A1~B1~C1
D1 | C1 | 3 | A1~B1~C1~D1
C2 | B1 | 2 | A1~B1~C2
C5 | B1 | 2 | A1~B1~C5
B3 | A1 | 1 | A1~B3
B4 | A1 | 1 | A1~B4
(8 rows)
test=# SELECT * FROM connectby('nodes','childid','parentid','B1',0,'~') AS
t(childid varchar, parentid varchar, level int, branch text);
childid | parentid | level | branch
-+--+---+--
B1 | | 0 | B1
C1 | B1 | 1 | B1~C1
D1 | C1 | 2 | B1~C1~D1
C2 | B1 | 1 | B1~C2
C5 | B1 | 1 | B1~C5
(5 rows)
HTH,
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
