Re: Fwd: Re[2]: [SQL] We all are looped on Internet: request + transport = invariant
If these people really work with hierarchically structured data, let them try a hierarchical database (even though PostgreSQL is your favourite database). They will profit 100fold from the advantages such a database has for such data. I now several biologists specialized in taxonomy (which is by nature hierarchically structured) and they all use a hierarchical database for their data. If these people want to output relational data in a hierarchical way (eg XML), they will have to learn sql (to manage their data in a relational database) and some xml library (in Perl, PHP, ASP or whatever they like most) to transform what the database outputs into the way they want it to look. Especially such people are quick learners and stating they can't do that is simply unbelievable. Anyway there should always be some IT guy in the neighbourhood to help them out. I suppose that would be you. Once again, don't try to let your sportscar fly. While it probably can be done, using a plane is so much more logical. >>> Dmitry Turin <[EMAIL PROTECTED]> 2007-04-20 17:59 >>> TML was born in discuss with these users (mainly nucleus physicists, optics , molecular biologists, zoologist and ecologist)
Re: [SQL] Retrieve month from date in SQl query
On Wed, Apr 18, 2007 at 10:36:14AM -0700, RPK wrote:
> I have a table called "StudentFeesPayment" with columns "ReceiptNo" and
> "ReceiptMonthYear".
> The column, "ReceiptMonthYear" stores date in the format "-mm-dd". I
> have to find the max(ReceiptNo) where Month of (ReceiptMonthYear)=4. Or
> whatever month I give.
>
> I tried:
>
> Select max(ReceiptNo) from StudentFeesPayment where
> Extract(Month('ReceiptMonthYear'))=4;
>
> But it is not working. How to do it?
Please explain how it's not working -- how does the result differ
from your expectation? I'd guess you're getting a syntax error;
see the documentation for the correct way to use extract():
http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
--
Michael Fuhr
---(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] We all are looped on Internet: request + transport = invariant
Good day, Joe. >> Table must be created in traditional way (by "create table, alter >> table") and not through browser. >> User must use "create table", etc in database-terminal like "psql.exe". > By "in a special way" I meant that tables have referential constraints > to other tables and they appear to rely on synthetic keys. Referential constraints must be created also in traditional way and also not through browser (by "psql.exe"). >your examples have invalid PG SQL Yes, i know. I copy this examples from my page http://sql40.chat.ru/site/sql40/en/author/introduction_eng.htm (i wrote this page for all DBMS in general). >I would change your examples to use less abstract > data, like department/employee, customer/product/order/order_line This will not help. To my mind, forum of real database is place, when i can find programmer, which can (and maybe would like) add parser to database. We all together can make that, i hope to that. Dmitry Turin http://html6.by.ru http://sql4.by.ru ---(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] We all are looped on Internet: request+transport = invariant
I'll continue with the analogy It is not impossible to attach wings to a sportscar. When you do, you will probably get the sportscar flying. However: 1. Why would you even try, if airplanes (which are designed from scratch to fly) already exist. Just use them 2. If you try nevertheless, is getting your car to fly worth the effert it will cost. It might fly in the end, but it will never be a Phantom (or whatever plane you prefer). Making something that can fly, requires you to focus on that from scratch. A car was never designed to fly. So basically a flying car will still be a car and not a plane. So, since hierarchical databases do exist, why even bother trying to transform a relational database into something that resembles a hierarchical database. A real hierarchical will always be better suited to do the job than your addition to a relational database will be, simply because the hierarchical database was designed from scratch to do just that. >>> Dmitry Turin <[EMAIL PROTECTED]> 2007-04-23 12:07 >>> Good day, Bart. > A hierarchical is specialized in managing data which is > hierarchically structured, while a relational database is not. You don't answer to question (why is impossible to make addition for relational database?). Dmitry Turin http://html6.by.ru ( http://html6.by.ru/ ) http://sql4.by.ru ( http://sql4.by.ru/ ) http://computer2.by.ru ( http://computer2.by.ru/ )
Re: [SQL] We all are looped on Internet: request + transport = invariant
Hi Dmitry, On Mon, 2007-04-23 at 11:20 +0300, Dmitry Turin wrote: > >I would change your examples to use less abstract > > data, like department/employee, customer/product/order/order_line > > This will not help. > To my mind, forum of real database is place, "What we got here is ... failure to communicate." I may be wrong, but I suspect most of the people in this forum take a look at create table a ( id num primary key; data float; ); create table b ( id num primary key; ref num references a(id); data float; ); create table c ( id num primary key; link num references b(id); data float; ); or even create table a ( id integer primary key, data float); create table b ( id integer primary key, ref integer references a(id), data float); create table c ( id integer primary key, link integer references b(id), data float); and your attempts to derive XML from them and ask questions like "how will this help me in my day-to-day job" and "what do a, b and c represent or are analogous to?" and all they'll see is a hierarchy of some sort (as evident by several of the responses) and they'll dismiss it because they have to deal with more complex relationships and the relational model allows them to express such connections. OTOH, if you were to take a real-life example, and show you can generate XML from your proposed TML, I contend that then you'd find more people receptive to your ideas or at least able to criticize them from more concrete viewpoints. Take a look for example at Philip Greenspun's "SQL for Web Nerds". It's dated and its examples are based on Oracle syntax, but it's based on a real web application. Show us what TML can do for the users, bboard and classified_ads tables (http://philip.greenspun.com/sql/data-modeling.html), and how it can answer the simple and complex queries in the next two chapters (or as I said, pick a real-life example of your own) and then maybe we'll have a more fruitful conversation. 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
[SQL] plperlu hash problem
I have a little Perl problem. When I call function dbi_select_test like
SELECT * from dbi_select_test() I get the expected result.
However when I call SELECT * from dbi_select I get an error message
saying "ERROR: error from Perl function: setof-composite-returning Perl
function must call return_next with reference to hash at line 9." My
perl knowledge seems to be too limited to find the solution myself. Can
anyone help me out, please?
CREATE OR REPLACE FUNCTION "public"."dbi_select_test" () RETURNS SETOF
text AS
$body$
use DBI;
my $dbh =
DBI->connect('dbi:Oracle:bmssa;host=firev120-1.indicator.be;sid=mars',
'bmssa', '')
or die "Couldn't connect to database: " . DBI->errstr;
my $sth = $dbh->prepare('SELECT * FROM address')
or die "Couldn't prepare statement: " . $dbh->errstr;
$sth->execute() or die "Couldn't execute statement: " . $sth->errstr;
while (my $row = $sth->fetchrow_hashref) {
return_next($$row{'ADDRTABLEID'});
}
$sth->finish;
return;
$body$
LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
CREATE OR REPLACE FUNCTION "public"."dbi_select" () RETURNS SETOF
"bmssa"."ADDRESS" AS
$body$
use DBI;
my $dbh =
DBI->connect('dbi:Oracle:bmssa;host=firev120-1.indicator.be;sid=mars',
'bmssa', '')
or die "Couldn't connect to database: " . DBI->errstr;
my $sth = $dbh->prepare('SELECT * FROM address')
or die "Couldn't prepare statement: " . $dbh->errstr;
$sth->execute() or die "Couldn't execute statement: " .
$sth->errstr;
while (my %row = $sth->fetchrow_hashref) {
return_next($row);
}
$sth->finish;
return;
$body$
LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
CREATE TABLE "bmssa"."ADDRESS" (
"ADDRTABLEID" TEXT,
"ADDRRECID" TEXT,
"LINENUM" TEXT,
"TYPE" TEXT,
"NAME" TEXT,
"ADDRESS" TEXT,
"PHONE" TEXT,
"TELEFAX" TEXT,
"COUNTRY" TEXT,
"DEL_SWIFTNUMBER" TEXT,
"ZIPCODE" TEXT,
"STATE" TEXT,
"COUNTY" TEXT,
"TELEX" TEXT,
"URL" TEXT,
"PHONELOCAL" TEXT,
"CELLULARPHONE" TEXT,
"EMAIL" TEXT,
"TAXGROUP" TEXT,
"CITY" TEXT,
"STREET" TEXT,
"PAGER" TEXT,
"SMS" TEXT,
"REFZIPCODE" TEXT,
"DATAAREAID" TEXT,
"RECID" TEXT
) WITHOUT OIDS;
[SQL] Large journal as psql table. Good idea? Triggering.
Dear Postgres folks; I'm considering using a postgres table for something that could be done with a flat file. Is this a good idea? I have events on a machine "A", which need to be sent by an SMS/Cell Phone modem that's on a totally different machine "B". Potentially this is a job for a flat file FIFO. But I'm thinking that maybe it's a job for a database table. Each new row would be written with a status (10="new"). And that the modem process would poll for new rows. Problem is there will be lots of rows, but only a trivial few will be "new". The huge index file and the polling seem like a drag on the database, unless there is a way to optimize. On the good side, this scheme handles building the FIFO and the IPC (interprocess communication). Is is a good approach, using postgres as a glorified FIFO? Should the DB on machine "A" or "B"? Is there a way to trigger or lower the polling overhead (in this application the delay is user visible). The volume is low (a few messages a minute tops). Bryce ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Large journal as psql table. Good idea? Triggering.
If it's going to be too big for a database, then it's going to be worse using flat-files on a disk :) I'd suggest putting it in a database, and have 2 tables: 1) "New" messages to be sent 2) Archive messages That way the polling machine only has to wait for the database to scan the "New" table, then move them in to the Archive table once it has sent them. Depending how many records you're actually looking at, one table with an index would probably be fine :) ~p -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bryce Nesbitt Sent: Tuesday, 24 April 2007 09:12 I'm considering using a postgres table for something that could be done with a flat file. Is this a good idea? I have events on a machine "A", which need to be sent by an SMS/Cell Phone modem that's on a totally different machine "B". Potentially this is a job for a flat file FIFO. But I'm thinking that maybe it's a job for a database table. Each new row would be written with a status (10="new"). And that the modem process would poll for new rows. Problem is there will be lots of rows, but only a trivial few will be "new". The huge index file and the polling seem like a drag on the database, unless there is a way to optimize. ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Large journal as psql table. Good idea? Triggering.
> But I'm thinking that maybe it's a job for a database table. Each > new > row would be written with a status (10="new"). And that the modem > process would poll for new rows. Problem is there will be lots of > rows, > but only a trivial few will be "new". The huge index file and the > polling seem like a drag on the database, unless there is a way to > optimize. create index on ( ) where status = 'new'; this index will be very fast. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
