Christopher Sawtell <[EMAIL PROTECTED]> said:
> On Wed, 14 Mar 2001 01:38, Richard Huxton wrote:
> > From: "Christopher Sawtell" <[EMAIL PROTECTED]>
> >
> > > Please could a kind soul help me with this.
>
> [ ... ]
>
> > Note to readers: this is not a general service, I'm in a good mood ;-)
>
Thus spake Richard Huxton
> begin;
> insert into archive_foo (select * from foo where foo_id=1);
> delete from foo where foo_id=1;
> commit;
>
> Is probably the closest you could get.
>
> Alternatively, you could wrap the above up in a function and just go:
>
> select do_archive_foo(1);
Or
you can also do it by a shell script
grantall.sh:
## start of grantall.sh ###
#!/bin/ash
SQL="SELECT relname FROM pg_class WHERE (relkind = 'r' OR relkind = 'S')
AND relname !~ '^pg_' ORDER BY relname"
OBJ=`psql -t -c "${SQL}" $1`
# OBJ=`echo ${OBJ} | sed 's/EOF//g'`
OBJ=`echo ${OBJ} | se
> For a moment I thought you could do to_char(now(),'Day DD Month '
> but you're quite right you need to rtrim() the various pieces.
regression=# select to_char(now()-10,'Day DD Month ');
to_char
-
Sunday04 March 2001
(1 row)
regression=# s
Hello,
I have created a table
test1(fromuri varchar(350), tovalue(varchar(3000))
and I have created a btree index on the fromuri attribute.
The size of the table is 630kb while the size of the index is
417kb.
I execute the following query
select * from test1 where fromuri like 'http://ww%';
Hello all - I apologize for the newbie-esque question, but the debug output
from postgres when you have a bug in your PL/pgSQL procedure is none to
detailed.
I've created the following procedure and am getting an error when I try to
update the table. The error is something like "parse error near
> On Wed, Mar 14, 2001 at 10:33:49AM -0500, Bruce Momjian wrote:
> > It is hard to test this without the table schema, but I think you are
> > missing some END IF's in the code above. Those are not elif's, but
> > actual new IF's that need their own END IF, I think.
>
> Oh wow - this is almost l
> Don't you have one END IF too many?
No you don't ... I can't count this morning :-(
Weird. I don't see what's wrong either. Anyone?
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once wi
Don't you have one END IF too many?
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Tim,
> Hello all - I apologize for the newbie-esque question, but the debug output
> from postgres when you have a bug in your PL/pgSQL procedure is none to
> detailed.
It's an improvement over the query parser errors -- at least in
PL/pgSQL you get a line number! Although in your case, the l
On Wed, Mar 14, 2001 at 10:33:49AM -0500, Bruce Momjian wrote:
> It is hard to test this without the table schema, but I think you are
> missing some END IF's in the code above. Those are not elif's, but
> actual new IF's that need their own END IF, I think.
Oh wow - this is almost like going ba
> IF NEW.status_id <> OLD.status_id THEN
> IF new.status_id = 1 THEN
> UPDATE artifact_counts_agg SET open_count=open_count+1 \
>WHERE group_artifact_id=new.group_artifact_id;
> ELSE IF new.status_id = 2 THEN
> UPDATE artifact_counts_
From: "Alexaki Sofia" <[EMAIL PROTECTED]>
> I execute the following query
> select * from test1 where fromuri like 'http://ww%';
>
> I get the following message
> NOTICE: PortalHeapMemoryFree: 0x16563a8 not in alloc set!
> ERROR: AllocSetFree: cannot find block containing chunk
It's complain
From: "Tim Perdue" <[EMAIL PROTECTED]>
> Hello all - I apologize for the newbie-esque question, but the debug
output
> from postgres when you have a bug in your PL/pgSQL procedure is none to
> detailed.
>
> I've created the following procedure and am getting an error when I try to
> update the ta
> Bruce Momjian writes:
>
> > There is a PL/PgSQL HowTo that we are trying to integrate into the docs
> > for 7.1. Anyone have a URL or status on that?
>
> It's not a PL/pgSQL Howto, it's just a documentation how to port from
> Oracle's PL/SQL. The status is "done". The author expressed inter
Tom,
1.Where or how I can get pltcl.so? I have not find this file anywhere in
my
source except a pltcl.c.
2.Dose installation same as plpgsql?
i.e.
CREATE FUNCTION pltcl_call_handler () RETURNS OPAQUE AS
'/usr/local/pgsql/lib/pltcl.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'p
Bruce Momjian writes:
> There is a PL/PgSQL HowTo that we are trying to integrate into the docs
> for 7.1. Anyone have a URL or status on that?
It's not a PL/pgSQL Howto, it's just a documentation how to port from
Oracle's PL/SQL. The status is "done". The author expressed interest in
more wo
you can also do it by a shell script
grantall.sh:
## start of grantall.sh ###
#!/bin/ash
SQL="SELECT relname FROM pg_class WHERE (relkind = 'r' OR relkind = 'S')
AND relname !~ '^pg_' ORDER BY relname"
OBJ=`psql -t -c "${SQL}" $1`
# OBJ=`echo ${OBJ} | sed 's/EOF//g'`
OBJ=`echo ${OBJ} | se
This is related to the plpgsql project I was working on this morning. I'm
trying to create a rule, so that when a row is inserted into a certain table,
we also create a row over in a "counter table". The problem lies in getting
the primary key value (from the sequence) so it can be inserted in tha
> "Tom" == Tom Lane <[EMAIL PROTECTED]> writes:
>> For a moment I thought you could do to_char(now(),'Day DD Month '
>> but you're quite right you need to rtrim() the various pieces.
Tom> regression=# select to_char(now()-10,'Day DD Month ');
Tom>to_char
I have a table like:
run | seq | start| done
1415|261| 2001-01-29 12:36:55| 2001-02-07 13:02:38
1415|263| 2001-02-14 07:40:04| 2001-02-15 16:05:04
1415|264| 2001-02-16 16:05:05| 2001-03-08 16:34:03
1415|265| 2001-03-08 16:34:04|
Entirely untested, but how about replacing currval() in your first try
with nextval()? My theory is that the compilation of the function is
happening before the INSERT happens; therefore the sequence hasn't been
incremented yet; therefore there's no currval() for this backend
instance. If you use
Tim Perdue <[EMAIL PROTECTED]> writes:
> This is related to the plpgsql project I was working on this morning. I'm
> trying to create a rule, so that when a row is inserted into a certain table,
> we also create a row over in a "counter table". The problem lies in getting
> the primary key value (
On Wed, Mar 14, 2001 at 01:09:18PM -0500, Tom Lane wrote:
> Tim Perdue <[EMAIL PROTECTED]> writes:
> > This is related to the plpgsql project I was working on this morning. I'm
> > trying to create a rule, so that when a row is inserted into a certain table,
> > we also create a row over in a "cou
Tim Perdue <[EMAIL PROTECTED]> writes:
>> You probably should be using a trigger, not a rule at all.
> OK - so another rule like this one, is probably ill-advised as well? It seems
> a lot easier than going into the triggers:
> CREATE RULE forum_delete_agg AS
> ON DELETE TO forum
> DO UP
Tom,
You indicate trigger, rather than rule. Going by Momjian's book, he
indicates that rules are "...ideal for when the action affects other
tables." Can you clarify why you would use a trigger for this? I'm asking
because I have no clue how to use rules or triggers, but need one or the
othe
"Creager, Robert S" <[EMAIL PROTECTED]> writes:
> You indicate trigger, rather than rule. Going by Momjian's book, he
> indicates that rules are "...ideal for when the action affects other
> tables." Can you clarify why you would use a trigger for this?
Primarily because there's a need to get a
Tom, Bruce,
Is there any way to make use of the contents of a cursor in a PL/pgSQL
function? FETCH ... INTO isn't supported, according to the docs. Can I
use the dot notation, or something similar?
-Josh
--
__AGLIO DATABASE SOLUTIONS
George Young wrote:
...
>I need to produce a new table that coalesces immediately successive
>operations on a run into one, e.g.:
>
>run | start | done
> 1415| 2001-01-29 12:36:55| 2001-02-07 13:02:38
> 1415| 2001-02-14 07:40:04|
> 1747| 2001-02-15 09:14:39| 20
On 3/14/01, 4:28:03 PM, Josh Berkus <[EMAIL PROTECTED]> wrote regarding Re:
[SQL] PL/pgSQL "compilation error":
> Tim,
> > Hello all - I apologize for the newbie-esque question, but the debug
output
> > from postgres when you have a bug in your PL/pgSQL procedure is none to
> > detailed.
> I
>> Original Message <<
On 3/14/01, 5:24:12 PM, George Young <[EMAIL PROTECTED]> wrote regarding [SQL]
need to join successive log entries into one:
> I have a table like:
> run | seq | start| done
> 1415|261| 2001-01-29 12:36:55| 200
On 3/14/01, 5:04:49 PM, Jie Liang <[EMAIL PROTECTED]> wrote regarding Re:
[SQL] pl/Perl :
> Tom,
I'm not the organ grinder, but maybe the monkey can help ;-)
> 1.Where or how I can get pltcl.so? I have not find this file anywhere in
> my
> source except a pltcl.c.
Did you set the relevant par
Hi,
I have a table like this:
location| numbrochures | marketing
---+--+---
101 Asheville, NC |4 | NEWS
101 Asheville, NC |1 | TV
101 Asheville, NC |3 | RADIO
101 Asheville, NC |
On Wed, 14 Mar 2001, you wrote:
> On 3/14/01, 5:24:12 PM, George Young <[EMAIL PROTECTED]> wrote regarding [SQL]
> I need to join successive log entries into one:
> > I have a table like:
>
> > run | seq | start| done
> > 1415|261| 2001-01-29 12:36:55| 2001-02-07 13:02:
On Wed, Mar 14, 2001 at 09:17:33PM +, Richard H wrote:
>
> > I need to produce a new table that coalesces immediately successive
> > operations on a run into one, e.g.:
>
> > run | start | done
> > 1415| 2001-01-29 12:36:55| 2001-02-07 13:02:38
> > 1415| 2001-02-14 07:
Srikanth Rao wrote:
>Hi,
>I have a table like this:
> location| numbrochures | marketing
>---+--+---
> 101 Asheville, NC |4 | NEWS
> 101 Asheville, NC |1 | TV
> 101 Asheville, NC |
The URL shown below can not get through. Would you please check again. Thank
You
JACK
- Original Message -
From: "Marc Cromme" <[EMAIL PROTECTED]>
To: "'datactrl'" <[EMAIL PROTECTED]>
Sent: Wednesday, March 14, 2001 6:58 PM
Subject: RE: psql win32 version
> Yes - check
>
> http://www.
> I'm happy to do my bit on the documentation side, but I don't want to end
> up duplicating the cookbook - does anyone know the status of this? I'd
> guess 90% of plpgsql functions will be on the same "theme" as something
> in a cookbook so it's the perfect format for learning/cut & paste.
I
This one worked a minute ago:-
http://www.zeos.dn.ua/download/ipgsql-1.6.2.zip
On Thu, 15 Mar 2001 14:14, datactrl wrote:
> The URL shown below can not get through. Would you please check again.
--
Sincerely etc.,
NAME Christopher Sawtell
CELL PHONE 021 257 4451
ICQ UIN45863470
Probably worth noting that this could be normalized into at least 3 tables
from what I can tell.
Tim
On Wed, Mar 14, 2001 at 11:03:01PM +, Oliver Elphick wrote:
> Srikanth Rao wrote:
> >Hi,
> >I have a table like this:
> > location| numbrochures | marketing
> >-
Srikanth,
Aside from seconding Tim (your database is *not* normalized), the PIVOT
function is only available one of 2 ways:
SQL-ONLY, FIXED COLUMN: If you know your categories in advance, you can
write a complex view using outer joins, sub-selects, and aggregates.
Imagine that we know y
41 matches
Mail list logo
