Re: [SQL] bibliographic schema
> > we're looking for a SQL database schema for bibliographical references. > > the goal is to extract all the bibliographical references contained in > > our various existing pgsql scientific databases in only one specific > > database and to interconnect them with external keys and perl scripts. Your best bet might be to take a look at OSS library management solutions and see whether that fits your needs or is suitable as a starting point. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Ordering a record returned from a stored procedure - date
Kent Anderson wrote: Never mind, it requires on each side of the variable. You will be delighted to learn that "dollar quoting" is in 8.0, which allows you to do things like: CREATE FUNCTION AS $$ ...function body here without needing doubling of ' $$ LANGUAGE plpgsql; I believe you can nest them too so long as you change the included string: $UNIQUESYMBOL1$ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Kent Anderson Sent: Monday, October 18, 2004 1:32 PM To: [EMAIL PROTECTED] Org Subject: Re: [SQL] Ordering a record returned from a stored procedure - date issue I have the code working except for the date part of the where clause. Can anyone point out how yield_date = 10/18/2004 can be translated so the 10/18/2004 is coming from a variable? ie yield_date = '' ... variable with date -- Richard Huxton Archonet Ltd ---(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] problems using phpPgAmin
beyaNet wrote: Hi, to those of you that may be using the above named admin tool, any ideas why I am unable to login with the postgres username even though I have amended the pg_hb file? Are there any other admin tools out there that i could use on a a unix box? Have you restarted PostgreSQL? Have you copied 'conf/config.inc.php-dist' to 'conf/config.inc.php' and configured it? BTW, there is a mailing list for phpPgAdmin at: [EMAIL PROTECTED] -- Mark Gibson Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] bibliographic schema
Karsten Hilbert wrote: we're looking for a SQL database schema for bibliographical references. the goal is to extract all the bibliographical references contained in our various existing pgsql scientific databases in only one specific database and to interconnect them with external keys and perl scripts. Your best bet might be to take a look at OSS library management solutions and see whether that fits your needs or is suitable as a starting point. I'm not interested in manage a library. That kind of projects schema are mostly complex and lending and storing oriented. I just want to store scientific references in a satisfactory way. I'm surprised that all the scientific databases schema are simplistic for references, and that no-one has published or produced such a database schema, probably useful and reusable. so, I may have to create it, as said Josh, and publish it :-) thanks Guillaume ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Aggregate Function with Argument
David Siegal wrote:
I would like to create an aggregate function that returns a concatenation
of grouped values. It would be particularly useful if I could pass an
optional delimiter into the aggregate function.
I've managed to do this in two stages:
1. Collect the set of values into an array.
This can be done using a custom aggregate function, array_accum,
which is demonstrated within the PostgreSQL manual:
http://www.postgresql.org/docs/7.4/interactive/xaggr.html
But here it is again:
CREATE AGGREGATE array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);
It makes me wonder why this isn't a built-in aggregate???
2. Convert the array to a string.
Using the built-in function array_to_string:
http://www.postgresql.org/docs/7.4/interactive/functions-array.html
Example:
SELECT
team_number,
array_to_string(array_accum(member_name), ', ') AS members
FROM team
GROUP BY team_number;
You can also go full round-trip (delimited string -> set) using the
builtin function: string_to_array, and a custom pl/pgSQL function:
CREATE FUNCTION array_enum(anyarray) RETURNS SETOF anyelement AS '
DECLARE
array_a ALIAS FOR $1;
subscript_v integer;
BEGIN
FOR subscript_v IN array_lower(array_a,1) .. array_upper(array_a,1)
LOOP
RETURN NEXT array_a[subscript_v];
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql'
STRICT IMMUTABLE;
Example:
SELECT * FROM array_enum(string_to_array('one,two,three',','));
--
Mark Gibson
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] help with to_date and to_char
Im trying to do something very simple
I have a field called when_month (integer )
so I want to get the month name for the integer
this comes close to what I want
update mytable set myfield=to_char(current_timestamp,'MON');
the result is myfield is set to OCT
which is close to what I want but when I try to use a variable
update mytable set myfield=to_char(when_month,'MON');
or a char with value of '05' or '5'
update mytable set myfield=to_char('05','MON');
get error:
update mytable set myfield=to_date(when_month,'MON');
update mytable set myfield=to_char('05','MON');
as well as
update mytable set full_month= to_date('01/'05'/2004','DDMON');
all get errors
Please can anyone help?
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] the problem of createlang!
I have installed postgresql (version 7.4.5) on the Redhat linux platform. Now I want to create a database with name BBMF and create pl/pgsql procedure language to the BBMF The problem is listed follow: [EMAIL PROTECTED] postgresql-7.4.5]$ psql -l List of databases Database | Owner | Encoding ---+--+--- BBMF | develop | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII (3 rows) [EMAIL PROTECTED] postgresql-7.4.5]$ createlang plpgsql BBMF ERROR: Load of file /usr/local/pgsql/lib/plpgsql.so failed: /usr/local/pgsql/lib/plpgsql.so: undefined symbol: error_context_stack createlang: language installation failed [EMAIL PROTECTED] postgresql-7.4.5]$ file /usr/local/pgsql/lib/plpgsql.so /usr/local/pgsql/lib/plpgsql.so: ELF 32-bit LSB shared object, Intel 80386, version 1, not stripped [EMAIL PROTECTED] postgresql-7.4.5]$ What’s the matter ? Please help me!!!
[SQL] need query advice
Hello all, i've got a psql database, with a table including 6 rows named "zahl1 zahl2 zahl3 zahl4 zahl5 and zahl6" type integer There are about 10.000 entries in the table. In every entry, the numbers are uniq to each other. That means, if zahl1=1, then zahl2-6 cannot be also 1. Example: 1, 10, 78, 43, 2, 67 (all 6 numbers are different in the same line, never a dup) 56, 34, 78, 1, 77, 99(but repeating in the db tousand times of course) 1, 2, 3, 4, 5, 6 2, 3, 4, 99, 5, 6 ... $a ... $f are the variables, of the 6 numbers i am searching: The numbers that are given to search, are also uniq of course, or it would not make sense. I need all hits, where at leased 5 of the given 6 numbers are maching in every entry: select * from zaehlerzuordnung_snapshots where ((zahl1=$a or zahl2=$a or zahl3=$a or zahl4=$a or zahl5=$a or zahl6=$a) and (zahl1=$b or zahl2=$b or zahl3=$b or zahl4=$b or zahl5=$b or zahl6=$b) and (zahl1=$c or zahl2=$c or zahl3=$c or zahl4=$c or zahl5=$c or zahl6=$c) and (zahl1=$d or zahl2=$d or zahl3=$d or zahl4=$d or zahl5=$d or zahl6=$d) and (zahl1=$e or zahl2=$e or zahl3=$e or zahl4=$e or zahl5=$e or zahl6=$e)) or ((zahl1=$b or zahl2=$b or zahl3=$b or zahl4=$b or zahl5=$b or zahl6=$b) and (zahl1=$c or zahl2=$c or zahl3=$c or zahl4=$c or zahl5=$c or zahl6=$c) and (zahl1=$d or zahl2=$d or zahl3=$d or zahl4=$d or zahl5=$d or zahl6=$d) and (zahl1=$e or zahl2=$e or zahl3=$e or zahl4=$e or zahl5=$e or zahl6=$e) and (zahl1=$f or zahl2=$f or zahl3=$f or zahl4=$f or zahl5=$f or zahl6=$f)) or ((zahl1=$a or zahl2=$a or zahl3=$a or zahl4=$a or zahl5=$a or zahl6=$a) and (zahl1=$c or zahl2=$c or zahl3=$c or zahl4=$c or zahl5=$c or zahl6=$c) and (zahl1=$d or zahl2=$d or zahl3=$d or zahl4=$d or zahl5=$d or zahl6=$d) and (zahl1=$e or zahl2=$e or zahl3=$e or zahl4=$e or zahl5=$e or zahl6=$e) and (zahl1=$f or zahl2=$f or zahl3=$f or zahl4=$f or zahl5=$f or zahl6=$f)) or ((zahl1=$a or zahl2=$a or zahl3=$a or zahl4=$a or zahl5=$a or zahl6=$a) and (zahl1=$b or zahl2=$b or zahl3=$b or zahl4=$b or zahl5=$b or zahl6=$b) and (zahl1=$d or zahl2=$d or zahl3=$d or zahl4=$d or zahl5=$d or zahl6=$d) and (zahl1=$e or zahl2=$e or zahl3=$e or zahl4=$e or zahl5=$e or zahl6=$e) and (zahl1=$f or zahl2=$f or zahl3=$f or zahl4=$f or zahl5=$f or zahl6=$f)) or ((zahl1=$a or zahl2=$a or zahl3=$a or zahl4=$a or zahl5=$a or zahl6=$a) and (zahl1=$b or zahl2=$b or zahl3=$b or zahl4=$b or zahl5=$b or zahl6=$b) and (zahl1=$c or zahl2=$c or zahl3=$c or zahl4=$c or zahl5=$c or zahl6=$c) and (zahl1=$e or zahl2=$e or zahl3=$e or zahl4=$e or zahl5=$e or zahl6=$e) and (zahl1=$f or zahl2=$f or zahl3=$f or zahl4=$f or zahl5=$f or zahl6=$f)) or ((zahl1=$a or zahl2=$a or zahl3=$a or zahl4=$a or zahl5=$a or zahl6=$a) and (zahl1=$b or zahl2=$b or zahl3=$b or zahl4=$b or zahl5=$b or zahl6=$b) and (zahl1=$c or zahl2=$c or zahl3=$c or zahl4=$c or zahl5=$c or zahl6=$c) and (zahl1=$d or zahl2=$d or zahl3=$d or zahl4=$d or zahl5=$d or zahl6=$d) and (zahl1=$f or zahl2=$f or zahl3=$f or zahl4=$f or zahl5=$f or zahl6=$f)) ; well.. this query is working. But for sure totaly ineffictive written. I need the advice of your professionals to more simply that query. As next problem i have to search if already 4 of the given 6 numbers are machting the 6 numbers per entrie. But to write a query like i did above.. wount do the trick i guess. Any hints? i really need it soon :( greets, Yze ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] libpq-fe: PQgetvalue() ?
[EMAIL PROTECTED] wrote:
> hi
>
> does PQgetvalue() allocate memory rof its result, it returns ?
> the answer will help me in problem:
> should i free some cstring_variable if
> { cstring_variable=PQgetvalue(pgresult_variable,0,0); }
> and could i PQclear(pgresult_varible) while cstring_varible is in use.
>From the documentation:
The pointer returned by PQgetvalue points to storage that is part of
the PGresult structure. One should not modify the data it points to, and
one must explicitly copy the data into other storage if it is to be used
past the lifetime of the PGresult structure itself.
So no, you must not free your copy of the pointer returned by PQgetvalue,
and no, once you PQclear the result your pointer is not valid. You should
---(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] help with to_date and to_char
There might be a better way, but this should do what you want. And I
think that you can safely replace '05' with when_month.
select to_char( to_date( '05' || '/' || to_char( current_date,
'DD/' ), 'MM/DD/' ), 'MON' );
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Oct 12, 2004, at 7:20 PM, Lori wrote:
Im trying to do something very simple
I have a field called when_month (integer )
so I want to get the month name for the integer
this comes close to what I want
update mytable set myfield=to_char(current_timestamp,'MON');
the result is myfield is set to OCT
which is close to what I want but when I try to use a variable
update mytable set myfield=to_char(when_month,'MON');
or a char with value of '05' or '5'
update mytable set myfield=to_char('05','MON');
get error:
update mytable set myfield=to_date(when_month,'MON');
update mytable set myfield=to_char('05','MON');
as well as
update mytable set full_month= to_date('01/'05'/2004','DDMON');
all get errors
Please can anyone help?
---(end of
broadcast)---
TIP 8: explain analyze is your friend
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] the problem of createlang!
"Fang Genjie" <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] postgresql-7.4.5]$ createlang plpgsql BBMF > ERROR: Load of file /usr/local/pgsql/lib/plpgsql.so failed: > /usr/local/pgsql/lib/plpgsql.so: undefined symbol: error_context_stack Looks like you are trying to load a 7.4 plpgsql.so into a pre-7.4 backend. You sure you updated your server? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] help with to_date and to_char
Thomas F.O'Connell wrote:
There might be a better way, but this should do what you want. And I
think that you can safely replace '05' with when_month.
select to_char( to_date( '05' || '/' || to_char( current_date, 'DD/'
), 'MM/DD/' ), 'MON' );
Perhaps
select to_char(to_date('02', 'MM'), 'MON');
is better. When current_date is, say Aug 31 then
select to_char( to_date('05' || '/' || to_char(current_date, 'DD/'),
'MM/DD/' ), 'MON' );
returns 'MAR', which is probably not what you want.
This works with 7.3.2, 7.4.5 and 8.0beta2.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Oct 12, 2004, at 7:20 PM, Lori wrote:
Im trying to do something very simple
I have a field called when_month (integer )
so I want to get the month name for the integer
this comes close to what I want
update mytable set myfield=to_char(current_timestamp,'MON');
the result is myfield is set to OCT
which is close to what I want but when I try to use a variable
update mytable set myfield=to_char(when_month,'MON');
or a char with value of '05' or '5'
update mytable set myfield=to_char('05','MON');
get error:
update mytable set myfield=to_date(when_month,'MON');
update mytable set myfield=to_char('05','MON');
as well as
update mytable set full_month= to_date('01/'05'/2004','DDMON');
all get errors
Please can anyone help?
---(end of broadcast)---
TIP 8: explain analyze is your friend
---(end of broadcast)---
TIP 8: explain analyze is your friend
--
Edmund Bacon <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] help with to_date and to_char
Thanks Thomas it worked great ,even when I put in the var - when_month
:)
Lori
Thomas F.O'Connell wrote:
There might be a better way, but this should do what you want. And I
think that you can safely replace '05' with when_month.
select to_char( to_date( '05' || '/' || to_char( current_date,
'DD/' ), 'MM/DD/' ), 'MON' );
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Oct 12, 2004, at 7:20 PM, Lori wrote:
Im trying to do something very simple
I have a field called when_month (integer )
so I want to get the month name for the integer
this comes close to what I want
update mytable set myfield=to_char(current_timestamp,'MON');
the result is myfield is set to OCT
which is close to what I want but when I try to use a variable
update mytable set myfield=to_char(when_month,'MON');
or a char with value of '05' or '5'
update mytable set myfield=to_char('05','MON');
get error:
update mytable set myfield=to_date(when_month,'MON');
update mytable set myfield=to_char('05','MON');
as well as
update mytable set full_month= to_date('01/'05'/2004','DDMON');
all get errors
Please can anyone help?
---(end of broadcast)---
TIP 8: explain analyze is your friend
---(end of broadcast)---
TIP 8: explain analyze is your friend
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] help with to_date and to_char
Edmund Bacon wrote:
When current_date is, say Aug 31 then
select to_char( to_date('05' || '/' || to_char(current_date, 'DD/'),
'MM/DD/' ), 'MON' );
NUTS! that should have been
select ... to_date('02' || ...
{ cut, paste, ?edit? }
returns 'MAR', which is probably not what you want.
This works with 7.3.2, 7.4.5 and 8.0beta2.
--
Edmund Bacon <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] need query advice
argh, you could use contribs/intarray with a gist index... instead of N columns use an integer[] and gist-index it, then write the equivalent of : where (intersection of the search array with the data array) has at least 5 elements (or 4 elements) (or at least 4 elements order by the number of elements desc) in your table defs, you can express your unicity constraint on the array with : CHECK( uniq(yourarrau) = yourarrau AND length(yourarrau)=6 ) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Which type of functions are best?
Hi, I'm just starting out and am looking to speed up queries using either SQL functions or PLPGSQL functions. I have googled around and have not found a great answer saying that this is the way to go. I would like to use PREPARE/EXECUTE... but of course they only last for each connection, I would like something more permanent. Thanks for your input, J ---(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] help with to_date and to_char
Yup, even better. For some reason I gave up trying to_date( '02', 'MON'
), which clearly wasn't working.
Thanks for the improvement!
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Oct 19, 2004, at 6:00 PM, Edmund Bacon wrote:
Thomas F.O'Connell wrote:
There might be a better way, but this should do what you want. And I
think that you can safely replace '05' with when_month.
select to_char( to_date( '05' || '/' || to_char( current_date,
'DD/' ), 'MM/DD/' ), 'MON' );
Perhaps
select to_char(to_date('02', 'MM'), 'MON');
is better. When current_date is, say Aug 31 then
select to_char( to_date('05' || '/' || to_char(current_date,
'DD/'),
'MM/DD/' ), 'MON' );
returns 'MAR', which is probably not what you want.
This works with 7.3.2, 7.4.5 and 8.0beta2.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Cross tabulations
Dear all,I need to do something similar to a cross tabulation, but without anyaggregation.I have below table id | employee_id | state | check_time+-+---+ 21 | 1 | In | 2004-10-12 21:37:13 22 | 1 | Break Out | 2004-10-12 21:37:31 23 | 1 | Break In | 2004-10-12 21:37:42 24 | 1 | Out | 2004-10-12 21:37:50 25 | 1 | In | 2004-10-13 19:20:36 26 | 1 | In | 2004-10-14 01:33:48 27 | 1 | Break Out | 2004-10-14 01:59:15 28 | 1 | Break In | 2004-10-14 03:15:45 29 | 1 | Out | 2004-10-14 03:17:23 30 | 3 | In | 2004-10-14 03:17:43 31 | 3 | Break Out | 2004-10-14 19:32:34 32 | 2 | In | 2004-10-14 20:34:15 33 | 3 | In | 2004-10-15 02:01:28 34 | 3 | Break Out | 2004-10-15 02:02:07 35 | 3 | In | 2004-10-16 02:06:43 36 | 1 | In | 2004-10-16 02:07:33 37 | 1 | Break Out | 2004-10-16 02:09:09 38 | 1 | Break In | 2004-10-16 04:10:21 39 | 1 | Out | 2004-10-16 04:12:27 40 | 3 | Break Out | 2004-10-16 21:38:22 I need something like this: date | employee_id | in | break_out | break_id | out---+-+--+---+--+--2004-10-12 | 1 | 21:37:13 | 21:37:31 | 21:37:42 |21:37:502004-10-14 | 1 | 01:33:48 | 01:59:15 | 03:15:45 |03:17:232004-10-14 | 3 | 03:17:43 | 19:32:34 | 03:15:45 |03:17:23
Re: [SQL] Cross tabulations
"Muhyiddin A.M Hayat" <[EMAIL PROTECTED]> writes: > Dear all, > > I need to do something similar to a cross tabulation, but without any > aggregation. join your table to itself four times: select * from (select check_time::date as date, employee_id, check_time-check_time::date as in from test where state = 'In') as a join (select check_time::date as date, employee_id, check_time-check_time::date as break_out from test where state = 'Break Out') as b using (employee_id,date) join (select check_time::date as date, employee_id, check_time-check_time::date as break_in from test where state = 'Break In') as d using (employee_id,date) join (select check_time::date as date, employee_id, check_time-check_time::date as out from test where state = 'Out') as e using (employee_id,date) ; Note that this will do strange things if you don't have precisely four records for each employee. Alternatively use subqueries: select date, employee_id, (select check_time-check_time::date from test where employee_id = x.employee_id and check_time::date = date and state = 'In') as in, (select check_time-check_time::date from test where employee_id = x.employee_id and check_time::date = date and state = 'Break Out') as break_out, (select check_time-check_time::date from test where employee_id = x.employee_id and check_time::date = date and state = 'Break In') as break_in, (select check_time-check_time::date from test where employee_id = x.employee_id and check_time::date = date and state = 'Out') as out from (select distinct employee_id, check_time::date as date from test) as x; This will at least behave fine if there are missing records and will give an error if there are multiple records instead of doing strange things. Neither of these will be particularly pretty on the performance front. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
