[EMAIL PROTECTED] wrote:
hi,
I want to make the following thing : select-based updatable VIEW,
which have two more virtual-fields. One of them is concatenation of
others and the second is calculated on the fly. Can I do this and if
yes how? can u give some example?
Here is the test bed :
table1)
NOTE THAT if field2 or fieldA might contain NULL values u should use
coalesce if u don't want to have a NULL value if one of the fields is NULL:
If field2 and fieldA are strings you will have something like that
(coalesce(t1.field2,'') ||coalesce(t2.fieldA,'')) AS stuff
[EMAIL PROTECTED]
SELECT ...
CASE
WHEN date CURRENT_DATE THEN 'green'::text
WHEN date CURRENT_DATE THEN 'red'::text
ELSE 'blue'::text
END
AS state,
(t1.field2 || t2.fieldA) AS stuff
FROM ...
]- aha thanx..
BOTH state and stuff will be only available for SELECTs on the
Hi
I faintly remember that I once stumbled upon a way to declare variadic
functions (functions that take a variable number of arguments) in plpgsql -
but I just searched the docs and can't find any reference to variadic
functions.
So - please enlighten me - are there variadic functions in
On Fri, Jun 18, 2004 at 16:13:38 +0300,
[EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
hi,
I want to make the following thing :
select-based updatable VIEW, which have two more virtual-fields.
One of them is concatenation of others and the second is calculated on the fly.
Can I do this and if
Hi
One of our production systems was running 7.4.1 for a few months, when
suddenly some queries that used a specifiy table (a cache table) started
crashing the backend.
A colleague of mine fixed the problem by simply dumping and rebuilding
the affected table (That was possible since it was only
[EMAIL PROTECTED] writes:
This is part of the Columns View, if you add a numeric field to your table
and don't provide any Length or Precision then :
numeric_precision is returned as 65535
numeric_scale is returned as 65531
Yeah, that's what you'd get for a numeric field with no length
On Fri, Jun 18, 2004 at 11:42:29 -0400,
Tom Lane [EMAIL PROTECTED] wrote:
The SQL spec doesn't allow unconstrained lengths for these types
so it gives no guidance about what to display in the information_schema
views. Any opinions?
It might make some sense to use the maximum length
Ron Snyder [EMAIL PROTECTED] writes:
That's all just background for my real question-- is there anything in the
standards (or elsewhere) that says you can't put binary(**) data into a char
field? When I changed the field to a bytea, processing time was
significantly reduced.
bytea is the
How may I do a INSERT ON DUPLICATE KEY UPDATE like in mysql:
http://dev.mysql.com/doc/mysql/en/INSERT.html
?
Eduardo
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Title: RE: [GENERAL] virtual fields on VIEW?
If I understand you correctly I believe this will work for you.
create view as select
t1.id, t1.date, t1.field1, t1.field2,
t2.fieldA, t2.fieldB,
-- state, stuff
case
when t1.date current_date then 'red'
when t1.date current_date
Harald Fuchs wrote:
In article [EMAIL PROTECTED],
Rory Campbell-Lange [EMAIL PROTECTED] writes:
I should have mentioned that we need the messages sent very soon after
they have landed in the 'inbox'; otherwise cron would definitely be the
way to go -- including an @reboot line.
This rules out a
Florian G. Pflug [EMAIL PROTECTED] writes:
I faintly remember that I once stumbled upon a way to declare variadic
functions (functions that take a variable number of arguments) in plpgsql -
but I just searched the docs and can't find any reference to variadic
functions.
Nope. You can sort of
Title: RE: [GENERAL] INSERT ON DUPLICATE KEY UPDATE
I would suspect you would need to write a trigger to do this.
-Original Message-
From: Eduardo Pérez Ureta [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 18, 2004 9:38 AM
To: [EMAIL PROTECTED]
Subject: [GENERAL] INSERT ON DUPLICATE
On 7.4.2 I have a trigger that I want to update any existing boolean
values to false if a new one in that group is declare true by inserting
a new record or updating an existing record:
ohc=# CREATE OR REPLACE FUNCTION public.clear_common_groups ()
RETURNS trigger AS'
ohc'# BEGIN
ohc'# IF
On 2004-06-18 17:19:40 UTC, Duane Lee - EGOVX wrote:
I would suspect you would need to write a trigger to do this.
It seems the mysql way of doing this is easier and safer.
Why is that not implemented in postgresql?
Is it better done with a trigger or with any other way?
Eduardo
-Original
Eduardo Pérez Ureta wrote:
On 2004-06-18 17:19:40 UTC, Duane Lee - EGOVX wrote:
I would suspect you would need to write a trigger to do this.
It seems the mysql way of doing this is easier and safer.
And non-standard AFAIK.
Why is that not implemented in postgresql?
Is it better done with a
On Fri, 2004-06-18 at 13:17, Robert Fitzpatrick wrote:
ohc=# CREATE OR REPLACE FUNCTION public.clear_common_groups ()
RETURNS trigger AS'
ohc'# BEGIN
ohc'# IF NEW.common_area = ''t'' THEN
ohc'# UPDATE tblhudunits SET common_area = ''f'' WHERE
hud_building_id = NEW.hud_building_id;
Florian G. Pflug [EMAIL PROTECTED] writes:
... I upgraded to 7.4.2, and fixed the system-tables
according to the 7.4.2 release-note. But this didn't really help - the
analyze table issued after fixing the system-tables exited with an
error about an invalid page header in one of our tables.
Tom Allison wrote:
Doug McNaught wrote:
Jonathan Barnhart [EMAIL PROTECTED] writes:
I know that postgres runs under Cygwin. It was announced that 7.4 would
have a windows native version or some such. I have found nothing but
Cygwin versions however.
Native Windows support is slated for 7.5
Title: RE: [GENERAL] INSERT ON DUPLICATE KEY UPDATE
I agree. You could always do a SELECT and if the record was found then UPDATE otherwise INSERT. A little more effort than MYSQL but again I don't believe the way MYSQL is allowing you to do it is standard.
Duane
-Original Message-
[EMAIL PROTECTED] (Scott Marlowe) wrote in message news:[EMAIL PROTECTED]...
On Thu, 2004-06-17 at 23:03, Deepa K wrote:
Hi,
I am using Postgresql 7.1.3. PgSQL server gets shut down once the hard
disk space becomes full. Is thers any particular reason to shut down the
server? Can any
I have a query that it seems is destined to be slow one way or another. I
have a table of around 30k articles, categorized by topic and ordered by date:
create table articles (
topic varchar(50),
created date,
data text
);
create index articles_topic_idx on articles(topic);
create index
It might be worthwhile to experiment with 2 new indexes:
Create UNIQUE index articles_created_topic_idx on articles(created,
topic);
Create UNIQUE index articles_topic_created_idx on articles(topic,
created);
Probably, one of the two should become your primary key.
That will give the optimizer
On 18/06/04, Harald Fuchs ([EMAIL PROTECTED]) wrote:
In article [EMAIL PROTECTED],
Rory Campbell-Lange [EMAIL PROTECTED] writes:
I should have mentioned that we need the messages sent very soon after
they have landed in the 'inbox'; otherwise cron would definitely be the
way to go --
On Fri, 18 Jun 2004, Jeff Rogers wrote:
create table articles (
topic varchar(50),
created date,
data text
);
create index articles_topic_idx on articles(topic);
create index articles_created_idx on articles(created);
If I want to get the 5 most recent articles in a topic, I get a
IIRC the standard syntax is based on db2's horrendous merge on command,
which was only added to the standard a couple months back.
IIRC the main downside to the select/update method is it introduces a
race condition that can only be solved by locking the table; not an
issue for most my$ql apps
27 matches
Mail list logo
