s.arizona.edu/~rts/tdbbook.pdf
Richard Snodgrass is one of the leading experts in the field. I warn you - the
book is heavy going - but so worth it!!
Keith
On Wednesday 07 November 2007 13:22, Philippe Lang wrote:
> Hi,
>
> Does anyone have experience, tips, links, regarding how to bui
< B2.tt_stop;
And if run on the data below, should pull out customer_no's '2' and '3'.
But does not seem to select any of the rows in which there are gaps in
Customers during the validity of Prop_Owner??
The data I used is as follows:
Customers:
customer_no |customer_na
,
sum(col_b) AS col_b_total
FROM foo
WHERE foobar
GROUP BY EXTRACT(week FROM col_a)
ORDER BY EXTRACT(week FROM col_a);
--
Kind Regards,
Keith
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
On Wed, 28 Jun 2006 10:48:31 -0700, Bricklen Anderson wrote
> Keith Worthington wrote:
> >>> "Keith Worthington" <[EMAIL PROTECTED]> writes:
> >>> The following is a section of code inside an SQL function.
> >> On Wed, 28 Jun 2006 12:16:29 -040
> > "Keith Worthington" <[EMAIL PROTECTED]> writes:
> > The following is a section of code inside an SQL function.
>
> On Wed, 28 Jun 2006 12:16:29 -0400, Tom Lane wrote
> SQL, or plpgsql? It looks to me like misuse of the plpgsql
bom
LEFT JOIN peachtree.tbl_mesh
ON tbl_item_bom.item_id = tbl_mesh.item_id
WHERE tbl_item_bom.so_number = rcrd_line.so_number
AND tbl_item_bom.so_line = rcrd_line.so_line
AND tbl_item_bom.component_type = 'net';
Kind Regards,
Keith
---(end of
G'day,
Looking for an example showing how to return a set from either a sql
function or a plpsqq function.
Thanks
--
Keith Hutchison
http://balance-infosystems.com http://realopen.org
http://www.kasamba.com/Keith-Hutchison
---(end of broa
wing procedure to extend a view: - drop depending
> rules - drop view - recreate view with additional column - recreate
> all rules
>
> Any help is welcomed!
>
> Thanks,
> Andreas Roth
Andreas,
I believe that is exactly what you have to do.
Kind Regards,
Keit
niqueness constraint violations.
>
> ~ TIA
>
> ~ Ken
Ken,
effective_date_and_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT
('now'::text)::timestamp(6) with time zone
Kind Regards,
Keith
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Michael Glaesemann wrote:
On Dec 8, 2005, at 11:03 , Keith Worthington wrote:
Is there a better way to do this? The CASE seems inefficient and
wordy but perhaps not.
I was hoping for
day_name(tbl_detail.ship_by_date) || tbl_detail.ship_by_date::text AS
sort_by_string
but AFAIK
D AS sort_by_string
Is there a better way to do this? The CASE seems inefficient and wordy
but perhaps not.
I was hoping for
day_name(tbl_detail.ship_by_date) || tbl_detail.ship_by_date::text AS
sort_by_string
but AFAIK day_name(date) or something like it is merely my fantasy. ;-)
RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION public.tf_update_row_count() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION public.tf_update_row_count() TO postgres;
GRANT EXECUTE ON FUNCTION public.tf_update_row_count() TO public;
-- Insert some initial data into
Personally I feel that if this individual can't be bothered to white
list the postgresql.org domain they should be banned from the list.
Kind Regards,
Keith
Original Message
Subject:RE: Re: [SQL] Rule
Date: Wed, 8 Jun 2005 19:02:39 -0300 (BRT)
From: AntiSpa
ata() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION interface.tf_audit_data() TO postgres;
GRANT EXECUTE ON FUNCTION interface.tf_audit_data() TO public;
CREATE TRIGGER tgr_audit_data
BEFORE INSERT OR UPDATE
ON sales_order.tbl_line_item
FOR EACH ROW
EXECUTE PROCEDURE interface.tf_audit_d
[snip]
LOOP
RETURN NEXT r_row;
END LOOP;
END IF;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
Although I have no need to limit the output I tried it just for giggles and it
worked fine.
SELECT * FROM func_bom(12345,
and thus, the query fails.
>
> Is there a way to set a default value to be inserted into myColumn
> ifand when "select altColumn ..." returns zero rows?
>
> Mark
Mark,
I do not know if it will work but I would try the COALESCE function.
http://www.postgresql.org/docs/8
remove the
quotes around that field. 2) Import the data into an intermediate table and
then using an after trigger move and manipulate the data using CAST. 3)
Import the data into your table using a BEFORE trigger and manipulate the data
using CAST.
HTH
Kind Regards,
Keith
-
s the last person working on it.
but I keep getting MySQL error # - Invalid use of group function
John,
I may be missing something but how about
SELECT count(id) AS unfinished
FROM work
WHERE userid = 'user1'
AND finished = 0
GROUP BY jobid;
--
Kind Regards,
Keith
--
ION. Something like (untested)
create function truncate_my_table() returns void as
$$ truncate my_table $$ language sql security definer;
You'd probably then revoke the default public EXECUTE
rights on this function, and grant EXECUTE only to
selected users.
--
Kind Regards,
Keith
-
'discounts' AS category,
-1 * sum( COALESCE(item.discount, 0) ) AS subtotal
FROM transaktion
LEFT OUTER JOIN item
ON ( transaktion.transaktion_pk = item.transaktion_fk )
WHERE transaktion.kind = 'S'
AND transa
an you provide a simple
example?
And all this time I thought that you couldn't write to a view.
Kind Regards,
Keith
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's
> > Is there a more efficient SQL statement that accomplishes the
> > same limiting functionality?
> >
> > Kind Regards,
> > Keith
> >
>
> Scott wrote:
> The in() construct is (nowadays) basically the same as
> ORing multiple columns;
>
> where column1
Hi All,
In several of my SQL statements I have to use a WHERE clause that contains
mutiple ORs. i.e.
WHERE column1 = 'A' OR
column1 = 'B' OR
column1 = 'C'
Is there a more efficient SQL statement that accomplishes the same limiting
funct
erprise Linux v3 on a
Dell with '2 PROCESSOR, 80532K, 2.8GHZ, 512K, 533, DECISION ONE'.
So far so good. :-)
Kind Regards,
Keith
---(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
have the affect of increasing PostgreSQL's use by
reducing the immediate porting requirements. I do not know if this is
possible but it would be cool stuff.
Kind Regards,
Keith
__
99main Internet Services http://www.99main.com
--
tion on result status.
http://www.postgresql.org/docs/7.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
Kind Regards,
Keith
PS I am a novice myself so don't put too much faith in my post. ;-)
__
99main Internet Services http:
EF34 | 0
GH12 | 4
JK56 | 0
Kind Regards,
Keith
__
99main Internet Services http://www.99main.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
,
Keith
__
99main Internet Services http://www.99main.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
se LIKE '3%' OR phrase LIKE '4%' OR phrase LIKE '5%' OR
phrase LIKE '6%' OR phrase LIKE '7%' OR phrase LIKE '8%' OR phrase LIKE
'9%';
Thank you.
Keith
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
dear all,
How to create a query that would join two databases? In MSSQL, i use
select * from dbA.dbo.tableA a dbB.dbo.tableB b where a.id=b.id
Is there anyway to write the above query in postgressql ?
Please email me back.
Thanks,
Keith
---(end of broadcast
witch, which will display the queries
it's sending to the backend. man psql for more details.
--keith
--
[EMAIL PROTECTED]
public key: http://wombat.san-francisco.ca.us/kkeller/kkeller.asc
alt.os.linux.slackware FAQ: http://wombat.san-francisco.ca.us/cgi-bin/fom
--
L work around?
Can I help this to happen?
--
Keith Gray
Technical Services Manager
Heart Consulting Services
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
rapped in a VB6 ADO ODBC program.
Is this likely to be sorted in 7.2 ?
Is anyone looking at this?
--
Keith Gray
Technical Services Manager
Heart Consulting Services
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
neous lookup?
--
Keith Gray
Technical Services Manager
Heart Consulting Services
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Josh Berkus wrote:
>>But EXISTS is an entirely different animal which is often faster
>>... isn't that in the FAQ?
There is no reference to EXISTS in the SELECT documentation??
Is this explained somewhere else?
--
Keith Gray
Technical Services Manager
Heart Co
We have moved from 7.1 to 7.2 and get the following error when
extracting dates.
Bad timestamp external representation ' '
eg. INSERT INTO mytable VALUES('1', '2001-09-24')
Seems to accept dd/mm/
(What about ISO default?)
--
Keith Gray
Technical Ser
d the
> names at which point you should always use double quotes to refer
> to it (yes, if the name was "foo" you *can* refer to it as foo, but
> you really shouldn't).
Your table names are case sensitive.
PostgreSQL will make them all lowercase by defa
.description, stock.available
FROM item, stock
WHERE item.itemid = '1234'
AND item.itemid=stock.itemid;
...would be more efficient than,
SELECT item.description, stock.available
FROM item, stock
WHERE item.itemid=stock.itemid
AND item.itemid = '1234';
--
Keith Gray
Technical S
ower(name) = 'jason';
>
How would PostgreSQL know to use the index
MyTable_lower_idx when I do a ...
SELECT * FROM MyTable WHERE lower(name) LIKE 'jas%';
--
Keith Gray
Technical Development Manager
Heart Consulting Services P/L
mailto:[EMAIL PROTECTED]
Josh Berkus wrote:
>
> To help remedy this, ...
> Can anyone suggest something?
>
Could we set-up a forum with a product like Request Tracker
where a group of experienced users could take questions
from a web-based queue?
--
Keith Gray
Technical Development Manager
Heart Consult
obviously a bit simplistic in my approach.
--
Keith Gray
Technical Development Manager
Heart Consulting Services P/L
mailto:[EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
The install for PgAdmin wont run without MDAC 2.5??
MDAC 2.5 is installed as part of an upgrade(?) to
Internet Explorer 5.5
I have IE5.5 but cannot install PgAdmin.
What is the exact dependency?
Can I force an Install?
--
Keith Gray
Technical Development Manager
Heart Consulting Services
n't true.
Does anyone have any insight as to how I can 'force' postgres to use Index
Backward Scan for #2???
Or, perhaps another method of making my ORDER BY faster ??
Your help would be greatly appreciated, Thanks
--
Keith Bussey
[EMAIL PROTECTED]
Programmer - WISOL.com
(5
suggestions would be welcomed (including upgrades)
--
Keith Gray
Technical Development Manager
Heart Consulting Services P/L
mailto:[EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org
named table] into droptemp;
drop [named table];
select * from droptemp into [named table];
Would this be available or easily written as a "rule" ??
Could anyone familiar with the code comment?
--
Keith Gray
---(end of broadcast)---
TIP 5
Roberto Mello wrote:
>
> What the heck is ipsql??
>
> -Roberto
ipgsql
A Win32 client for interactive Postgres session
Keith
README
Interactive PostgreSQL presents comfortable windows environment
to execute sql queries, edit table
ql...
it doesn't handle update, but does handle select.
Both work fine for psql (linux).
Keith.
---(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
Tom Lane wrote:
>
> Keith Gray <[EMAIL PROTECTED]> writes:
> > Is it possible to get/configure PostgreSQL to handle
> > as within a dleimited string?
>
> We already do.
>
> regression=# select 'O''SHEA';
> ?column?
> --
set code = 'O''SHEA' where clientid = 2;
Is it possible to get/configure PostgreSQL to handle
as within a dleimited string?
Keith Gray
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
ad of creating a new database connection
each time.. it will try to use
an existing connection that is no longer being used (persistent connections
do tend to have a lot of quirks tho)
Keith
At 11:33 AM 18/04/2001 +0200, Picard, Cyril wrote:
>Hi all, sorry for the maybe offtopic questions
for the Aged query?
e.g. CREATE INDEX Aged_ClientID ON Aged(ClientID);
Would this index be continually maintained by the RDBMS or only on lookup?
Keith
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Is it possible (feasible) to create an index on a view.
We have a large table and a defined sub-set (view)
from this table, would it be possible to keep an index
of the sub-set.
Keith
---(end of broadcast)---
TIP 4: Don't 'ki
ost stable 7.X release?
Keith
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Always- I think I'll use the to_char since I think you all are saying
that that is ISO or at least POSIX.
On Mon, 5 Feb 2001, Karel Zak wrote:
>
> On Mon, 5 Feb 2001 [EMAIL PROTECTED] wrote:
>
> > Ok, so there is actually two standards then. Is this documented
> > anywhere? Is this is someth
Ok, so there is actually two standards then. Is this documented
anywhere? Is this is something that is going to change? I don't want
to write and app and have things "break" during and upgrade :)
Thanks for the response.
On Mon, 5 Feb 2001, Karel Zak wrote:
>
> On Mo
erstanding
something. I was hoping that someone might be able to shead some light
on this. Thanks.
Keith C. Perry
VCSN, Inc.
http://vcsn.com
Is DROP Column implemented in 7.x?
Keith
Ahhh, thank you that worked. I don't know why but for some reason I didn't
think I could do a subquery in an insert *laff*- 'learn something new
everyday :)
Keith-
Ian Harding wrote:
> Could you not:
>
> insert into events (eid,name) values ((SELECT max(eid) FROM
but in the
interest not having to rewrite the code (or dump, drop and recreate the
tables/data), I wanted to know if there was a more stand way to
incrementing a field automatically that would be fairly portable. Any
help would be appreciated.
Keith Perry
VCSN Inc.
[EMAIL PROTECTED]
http://vcsn.com
Josh Berkus wrote:
>
>
> The solution to this is not to use BLOBs, but rather to use file system
> handles for the location of the binary data on the server. This way,
> all you need is DOS-to-UNIX and UNIX-to-DOS translation for the
> filesystem handles, something easily accomplished through
>
. but I don't know
about the input/output for zero and one.
Should SQL (ODBC) be able to ask "WHERE bitfield;"
or should it ask "WHERE bitfield = 1;" ?
Any response gratefully recognized...
Keith
though)
I'm not sure about the exact syntax, but if you look in past threads for
"execute" I'm sure you'll find it.
Have fun.
Merry Xmas.
Keith.
At 02:00 PM 22/12/2000 +0100, Volker Paul wrote:
> > Can this be done using tcl or perl?
>
>I'll try them and report what I find out.
>
>V.Paul
Do you mean VARCHAR[30] or VARCHAR(30)??
I think you're creating an array of chars...
but what you want is just a VARCHAR type with 30 characters...
It may explain the array error you are getting.
Keith
At 11:07 AM 21/11/2000 -0200, Aristeu Gil Alves Junior wrote:
>I´m trying to uplo
o work.
Let me know on your findings. I'll be interested to know.
Cheers,
Keith :)
At 12:34 PM 21/11/2000 +, Colleen Williams wrote:
>Hello all,
>
>I am having some problems with persistent connections.
>
>The PHP scripts in my content management system (CMS) are conne
nix socket.
Not sure about the persistant database connection stuff. What version of
Php are you using?
The only thing I could say, is to try explicitly closing the persistent
database connections in your Php code.
Maybe there is a bug in Php.
Hope this helps,
Keith.
At 12:34 PM 21/11/2000 +000
Do you want to have a field name called "SELECT"?
If so, you can use brackets in SQL 7 or higher. [SELECT]
You can also use quoted identifiers (and the double quote) "SELECT"
Keith
"lesstif" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">
This is an example script I use to keep a log of all database operations on
my tables.
Hope it helps.
At 05:05 PM 3/10/2000 +1000, Carolyn Lu Wong wrote:
>[EMAIL PROTECTED] wrote:
> >
> > Carolyn Lu Wong wrote:
> > >
> > > I need to write a trigger to create a new record in log table if it's a
>
Hi all,
Does anybody know the transaction isolation level default when a plpgsql
stored procedure is called?
Is it possible to set it? Or is this controlled by SPI?
Cheers,
Keith.
d before I called the stored procedure? I'm not even
sure that
would work, I assume plpgsql uses the current transaction isolation level?
Any help would be much appreciated :)
Cheers, Keith.
assume plpgsql uses the current transaction isolation level?
Any help would be much appreciated :)
Cheers, Keith.
This is not really possible with postgresql at the moment.
Better off trying to work around, perhaps using a view. That way you have a
way to change the select
statement without actually modifying your client code.
Keith.
At 06:09 PM 22/09/2000 -0400, Nelson wrote:
>thank you jie Liang
Anybody know how to compile pgaccess from postgres source files?
It must be a configure option, but I can't find it.
Keith.
ore stored procedure features? I'm not sure what your
application is exactly but there are ways
to work around the features that are missing.
Good luck with convincing your boss :)
Keith.
At 02:28 PM 29/08/2000 +0200, Andreas Tille wrote:
>On Mon, 28 Aug 2000, Yury Don wrote:
>
> >
m
on my Linux
Postgres database. It keeped on giving me...
an error found one line 1 near " "
which wasn't very helpful.
Anyway just make sure you write your stored procs in your Unix environment
or save them as Unix
format. I hope this tip saves somebody some time. :)
Cheers,
Keith.
hat I cannot insert a record into another table in a trigger.
I'm not sure why though.
Anybody else done similar operations within a trigger procedure? Or know of
a work around?
Cheers,
Keith.
of text.
The text stored needs to searchable as well.
Cheers,
Keith.
s using pl/pgsql?
If so, how do I declare the return type in the declaration?
Thanks in advance.
Cheers,
Keith.
77 matches
Mail list logo
