[SQL] Rules

2000-12-31 Thread Peeter Smitt 



Hi 
 
I'm trying to make updateable view useing 
rules.
 
CREATE RULE update_rule AS ON UPDATE TO table DO 
INSTEAD  SELECT fun1(new);
 
Thing is that backend gives this 
error.
  ERROR:  parser: parse error at or near 
")"
 
What i'm doing wrong? Are there any other ways to 
make updatebale multi-table views?
 
thanks and joyfull new year.
 
-Peeter 
SmittAS Delfi OnlinePärnu mnt. 15811317 Tallinn, EstoniaTel 
+3726501734GSM +37256647653[EMAIL PROTECTED]www.delfi.ee

Re: [SQL] Rules

2000-12-31 Thread Peter Eisentraut 

Peeter Smitt writes:

> CREATE RULE update_rule AS ON UPDATE TO table DO INSTEAD
>   SELECT fun1(new);
>
> Thing is that backend gives this error.
>   ERROR:  parser: parse error at or near ")"

Try double quoting "new", since it's a reserved word.

-- 
Peter Eisentraut  [EMAIL PROTECTED]   http://yi.org/peter-e/

[SQL] Unable to identify an ordering operator

2000-12-31 Thread Ferruccio Zamuner 

Hello,

select persons.name,firm.name,persons.tel
   from persons,work,firm 
   where (persons.table_owner=0) and 
 (work.id_firm=firm.id and work.id_person=persons.id) 
  union
   select persons.name,firm.name,persons.tel 
  from persons,work,firm 
  where (persons.table_owner=1) and 
(work.id_firm=firm.id and work.id_person=persons.id);

ERROR:  Unable to identify an ordering operator '<' for type '_text'
Use an explicit ordering operator or modify the query


I know that this query can be rewrite to eliminate the UNION using an
OR operator on first condition but my question is:

How can I specify an ordering operator? On which field have I to put it?


Best wishes for the new YEAR,  \fer

Re: [SQL] Unable to identify an ordering operator

2000-12-31 Thread Tom Lane 

Ferruccio Zamuner <[EMAIL PROTECTED]> writes:
> select persons.name,firm.name,persons.tel
>from persons,work,firm 
>where (persons.table_owner=0) and 
>  (work.id_firm=firm.id and work.id_person=persons.id) 
>   union
>select persons.name,firm.name,persons.tel 
>   from persons,work,firm 
>   where (persons.table_owner=1) and 
> (work.id_firm=firm.id and work.id_person=persons.id);

> ERROR:  Unable to identify an ordering operator '<' for type '_text'

UNION is defined to eliminate duplicate rows, so it has to be able to
sort and compare rows, which means it needs < and = operators for each
selected column's datatype.  Evidently one of the name or tel columns
is declared as array of text ('_text' means text[]), for which there
is no '<' operator.  You could probably make one if you were intent
on it (see contrib/array for some inspiration).  A lazier way out is
to use UNION ALL, which doesn't try to eliminate duplicates.

regards, tom lane

[SQL] Arrays

2000-12-31 Thread Albert REINER 

Saluton,

I somehow get the impressions that support for arrays is not a high
priority with PostgreSQL, even though I think it would be very
handy. On the other hand, the system itself uses arrays e.g. for user
groups, so I guess there should be readily available
functions/operators for dealing with that, but I cannot seem to be
able to find them.

Is there a simple way of checking whether e.g. a given int4 is in a
given int4-array?

Is there a way to "thread" a command over an array? By this I mean:
Given an array {1, 2, 3} and a table t like

   id   |  name
|
   1|  joe
   2|  fred
   3|  mark

, a call to select thread('||', t, {1,2,3}) should produce
'joefredmark'.

Thanks in advance,

Albert.


-- 

--
Albert Reiner   <[EMAIL PROTECTED]>
Deutsch   *   English   *   Esperanto   *   Latine
--

Re: [SQL] Arrays

2000-12-31 Thread Oleg Bartunov 

Wait some time. We have something you need - we implement RD-tree
using GiST interface. Unfortunately, GiST was very poorly tested
and supported, so we have to understand, fix and rewrite some part of code. 
There are still some problems but we hope to get working code in January.

Oleg
On Sun, 31 Dec 2000, Albert REINER wrote:

> Date: Sun, 31 Dec 2000 22:29:33 +0100
> From: Albert REINER <[EMAIL PROTECTED]>
> To: PostgreSQL-SQL <[EMAIL PROTECTED]>
> Subject: [SQL] Arrays
> 
> Saluton,
> 
> I somehow get the impressions that support for arrays is not a high
> priority with PostgreSQL, even though I think it would be very
> handy. On the other hand, the system itself uses arrays e.g. for user
> groups, so I guess there should be readily available
> functions/operators for dealing with that, but I cannot seem to be
> able to find them.
> 
> Is there a simple way of checking whether e.g. a given int4 is in a
> given int4-array?
> 
> Is there a way to "thread" a command over an array? By this I mean:
> Given an array {1, 2, 3} and a table t like
> 
>id   |  name
> |
>1|  joe
>2|  fred
>3|  mark
> 
> , a call to select thread('||', t, {1,2,3}) should produce
> 'joefredmark'.
> 
> Thanks in advance,
> 
> Albert.
> 
> 
> -- 
> 
> --
> Albert Reiner   <[EMAIL PROTECTED]>
> Deutsch   *   English   *   Esperanto   *   Latine
> --
> 

_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83