Re: [SQL] a wierd query
I understand, thanks. First: as I said in my previous post, the workhour_id was left behind by mistake and has since been removed. So it seems that I can ommit the distinct completely and just use the query in it's simpelest form, like this: select employee_id, begindate as date from workhour UNION select employee_id, enddate as date from workhour And I would get a list of all dates (as well begin- as enddates) where a date can occure only once with each employee? Altough I didn't start this thread I'm learing some usefull things here, so some thanks to the people who replied (and started the thread) are in place here. Kind regards, Stijn Vanroye > -Original Message- > From: Stephan Szabo [mailto:[EMAIL PROTECTED] > Sent: maandag 17 mei 2004 17:01 > To: Stijn Vanroye > Cc: [EMAIL PROTECTED]; Edmund Bacon > Subject: Re: [SQL] a wierd query > > On Mon, 17 May 2004, Stijn Vanroye wrote: > > > Are you sure about that Edmund? > > > > I have the following query: > > select distinct on (task_id, date) task_id, > workhour_id, date from > > ( > > select task_id, workhour_id, begindate as date from workhour > > UNION > > select task_id, workhour_id, enddate as date from workhour > > )as dist1 > > This gets you first rows distincted by task_id, workhour_id and date > and then only rows distincted by task_id and date (and an > unpredictable > workhour_id). > > > if I use the query without the top level select, like this: > > select task_id, workhour_id, begindate as date from workhour > > UNION > > select task_id, workhour_id, enddate as date from workhour > > This gets rows distincted by task_id, workhour_id and date. > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Opening and Fetching from a refcursor inside a function
Dear Friends, I am using Postgres 7.3.4 on Redhat linux In one of my requirement, I am getting a cursor as input parameter.I am trying to process the records inside this cursor and i am not able to do it. (Some thing like opening and fetching a cursor in SQL Server) My approach is as follows. fn_xyz(refcursor,int4) returns recordDeclaredata ALIAS $1;mview RECORD;FOR mview IN FETCH ALL IN dataLOOP//Process the records.END LOOP; Is this possible. Please shed some light. Thanks Kumar
[SQL] Very slow search using basic pattern matching
I'm trying to use the following statement as part of a search facility on a site I develop. However the search is taking in excess of 10 seconds some times to return results. I'ts a dual opteron server with a couple gigs of RAM so should be more than enough to deal with this database. SELECT da_records.RECORD_ID, da_records.TITLE_EN AS TITLE, da_records.AUTHOR_EN AS AUTHOR, da_records.DESCRIPTION_CY AS DESCRIPTION, da_records.PUBLISHER_CY AS PUBLISHER, da_records.URL_CY AS URL, da_records.RECORD_ID, da_records.KEYWORD_LIST_ID, da_records.LANGUAGE, da_records.CONTRIBUTOR_NAME, da_records.CONTRIBUTOR_EMAIL, da_records.CONTRIBUTOR_ORGANISATION, da_records.CONTRIBUTOR_CREDIT, da_records.DEWEY_LIST_ID, da_records.LISTING_PRIORITY, da_records.SUBMITTED_DATE, da_records.LAST_EDIT_DATE, da_records.STATUS FROM da_records WHERE da_records.DESCRIPTION_CY ~* '.*Aberystwyth*.' OR da_records.TITLE_CY ~* '.*Aberystwyth*.' limit 100 Is there a better way of matching the string? (Must be case insensitive) TIA -- Dan Field <[EMAIL PROTECTED]> - Support Programmer: Cymru ar y we cy_GB: http://www.cymruarywe.org en_GB: http://www.walesontheweb.org
Re: [SQL] Very slow search using basic pattern matching
On Tue, May 18, 2004 at 16:47:11 +0100, Dan Field <[EMAIL PROTECTED]> wrote: > FROM > da_records > WHERE > da_records.DESCRIPTION_CY ~* '.*Aberystwyth*.' > OR > da_records.TITLE_CY ~* '.*Aberystwyth*.' > limit 100 > > Is there a better way of matching the string? (Must be case insensitive) This is going to require a sequential scan and if you aren't going to hit 100 entries near the beginning of the table, you will be effectively doing a full scan of the table. You might try looking at the tsearch2 contrib entry (a package for full text searching) as that will likely be able to help you out. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] I'm baffled
I have a couple of 'c' object files that send mail when ever there in an
insert or delete to specified tables.
The problem is that I get the mail message even if the insert fails!
what am I doing wrong?
thanks
The function;
create function insert_r1(text, text, text, text) returns int4
as '/var/pgsql/insert_r1.so' LANGUAGE 'c' ;
the rule:
create rule partmaster_I as
on insert to partmaster
do select insert_R1(new.afpn, 'partmaster(insert)','','') as answer;
the 'c' module:
# include "internal/postgres_fe.h"
# include
# include
int insert_r1(text *arg1, text *arg2, text *arg3, text *arg4)
{
char *table, *key, *mesg, *user;
char host[20];
FILE *fp;
arg1->vl_dat[arg1->vl_len - VARHDRSZ] = '\0';
arg2->vl_dat[arg2->vl_len - VARHDRSZ] = '\0';
arg3->vl_dat[arg3->vl_len - VARHDRSZ] = '\0';
arg4->vl_dat[arg4->vl_len - VARHDRSZ] = '\0';
key = strdup(arg1->vl_dat);
table = strdup(arg2->vl_dat);
mesg = strdup(arg3->vl_dat);
user = strdup(arg4->vl_dat);
gethostname(host, sizeof(host));
system(" cat /tmp/newMsg >> /tmp/newMsg.arc");
if ( (fp = fopen("/tmp/newMsg", "w+") ) == NULL)
return(-1);
fprintf(fp, "%s:: %s:(%s) Approval requested - %s %s\n",
host,table,key,mesg,user);
fclose (fp);
system("Mail -s \"Bmss \" [EMAIL PROTECTED] < /tmp/newMsg");
return(0);
}
---(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] Very slow search using basic pattern matching
> WHERE
> da_records.DESCRIPTION_CY ~* '.*Aberystwyth*.'
> OR
> da_records.TITLE_CY ~* '.*Aberystwyth*.'
> limit 100
>
> Is there a better way of matching the string? (Must be case
> insensitive)
Are you sure you can't anchor the search pattern ?
eg ~* '^' || lower('Aberystwyth') || '.*'
That'd allow for use of (functional) indices.
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] I'M Sorry!
And sorry because the subject... lol. > On Tue, 2004-05-18 at 15:26, Ricardo Maia wrote: > > Hi, > > > > I'm from Brazil, and i have a question. > > > > Exists some problem if i not uses the option "without oids"? > > I read the manual, but I don't understand what I earn if i use it. > > > > > > Thanks, > > > > Ricardo. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Using "Without OIDs"
Ricardo Maia wrote: Hi, I'm from Brazil, and i have a question. Hi Ricardo, happy to help, but three points: 1. Always make sure you use "reply to all" on the PostgreSQL lists 2. For a new question, send a new message to [EMAIL PROTECTED], since most direct email to this address goes into my spam bin. 3. You caught the subject-line change yourself in your next posting. Exists some problem if i not uses the option "without oids"? I read the manual, but I don't understand what I earn if i use it. The OIDs are used by PostgreSQL's system tables. So, you can do something like SELECT oid,relname,reltype FROM pg_class; This will give you the number PG uses to refer to tables (etc.), the name you use and the type of thing it is. This type is also an OID. These OIDs were never really meant to be for user-tables, but some people used them as a default primary-key. This was a bad idea, but since the column was always there you couldn't stop them. The developers couldn't just turn OIDs off for user tables without breaking some applications, so they added an option to say you didn't want them. If you specify you don't want OIDs that table will use 4 bytes less for every row, and so be a bit faster. I believe in the next version, the default setting will be not to have OIDs and you will have to specify "WITH OIDS" if you do want them. HTH -- Richard Huxton Archonet Ltd ---(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
