Re: [SQL] help on creating table

[Albert REINER]

Saluton,

this is easy:

On Fri, Oct 20, 2000 at 06:48:54PM +0800, pgsql-sql wrote:
...
> NODE1
>+ --- NODE1_1
>+ --- NODE1_2
>|+ --- NODE1_2_1
>+ --- NODE1_3

create table n (id int4, parent int4, data text);

insert into n (id, data) values (1, 'node 1');
insert into n (id, parent, data) values (2, 1, 'node 1.1');
insert into n (id, parent, data) values (3, 1, 'node 1.2');
insert into n (id, parent, data) values (4, 3, 'node 1.2.1');
insert into n (id, parent, data) values (5, 1, 'node 1.3');

(you will probably want to use a serial for id, etc.)

The idea is to store each node in a row, and to store both the row's
id and the id of the parent node in it. When there is no parent node
(your node NODE_1), parent is NULLL.

HTH,

Albert.


-- 

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

Re: [SQL] Create tables in one query

[Albert REINER]

On Thu, Nov 23, 2000 at 11:40:52AM -0500, [EMAIL PROTECTED] wrote:
> On Thu, 23 Nov 2000, Tomasz Gralewski wrote:
> 
> > I'd like to create a few tables in one SQL query, is that possible.
> > What tools I have to use, Perl, Tcl, or maybe is there block command
> > separator that I can write:
> > CREATE TABLE abd
> >  (
> > atype,
> > btype,
> > ctype
> >  ) and I whant to add the next table here, how can I separate, by this (";"
> > that;s the end of table abd).
> 
> Not sure what you are trying to do here.  You can put statements like
...
> in a single file and load the file into psql via \i.  That's I build all
> of my databases.

Or, if you are concerned with possible failures: enclose the series of
CREATEs in a transaction.

Albert.


-- 

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

Re: [SQL] Tree structure

[Albert REINER]

On Mon, Sep 11, 2000 at 01:27:48PM +0100, Trewern, Ben wrote:
> Anybody know how to make a tree structure using related tables using
> Postgres.  Something like a directory structure is what I'm aiming for.  I'm
> sure there is an easy way but I'm having probs.

I am not quite sure whether this is what you are thinking about:

create table tree (
  id int4,
  parentid int4,
  data text)

A structure like

 a
 |__b
 |__c
 
can be achieved by

insert into tree (id, parentid, data) values (1, NULL, 'a');
insert into tree (id, parentid, data) values (2, 1, 'b');
insert into tree (id, parentid, data) values (3, 1, 'c');


Probably you'll also want to use a sequence for the ids, and to
declare indices on id, parentid etc.

> 
> Any help would be appreciated.
> 
> Ben.


-- 

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

[SQL] Documentation for CASE

[Albert REINER]

Saluton,

I think that the documentation for CASE is not very clear:

,[ postgres/doc.html/user/functions.htm#AEN2546 ]
|Table 5-1. SQL Functions
|Function Returns Description Example
| ...
|CASE  WHEN  expr  THEN expr [...] ELSE expr END expr return expression
|for  first  true  WHEN  clause  CASE WHEN c1 = 1 THEN 'match' ELSE 'no
|match' END
`

When building a statement with more than one clause, I was tempted to
write CASE WHEN a THEN b ELSE WHEN c THEN d ELSE e instead of the
correct CASE WHEN a THEN b WHEN c THEN d ELSE e.

Also, to be correct, the description should mention what the ELSE
clause is good for (though it's not difficult to guess).

I'm sorry to have bothered you if this was already corrected for more
recent versions - I'm running

,
| albert=> select version();
|   version
| 
|  PostgreSQL 7.0.2 on i586-pc-linux-gnulibc1, compiled by gcc 2.95.1
| (1 row)
`

Bye,

Albert.


-- 

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

Re: [SQL] Documentation for CASE

[Albert REINER]

On Sat, Dec 16, 2000 at 06:39:14PM +0100, Peter Eisentraut wrote:
> Albert REINER writes:
> 
> > I think that the documentation for CASE is not very clear:
> 
> Maybe you will like this better (from upcoming 7.1):
> 
> http://www.postgresql.org/devel-corner/docs/postgres/functions-conditional.htm

much better. Sorry not to have checked that first.

Albert.

-- 

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

[SQL] Changing owner of tables, functions, etc.

[Albert REINER]

Saluton,

is there a way to "donate" databases, tables, functions etc. to some
other postgres user?  The reason for this is that it would add some
safety with respect to preventing one from accidentally DROPping some
important data, as one would have to su to the other user first.

Thanks in advance,

Albert.


-- 

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

[SQL] PL/pgSQL: returning array?

[Albert REINER]

Saluton,

I am trying to write my first plpgsql-functions; I do not seem to
be able to find a way of returning an array; here is what I tried:

,
| CREATE FUNCTION "concatallinstances2" (int4 ) RETURNS text[2] AS '
| declare
|   reslt text;
|   separator text;
|   instance record;
|   first text;
| begin
| reslt := '''';
| separator := '''';
| for instance in select cat from t where id = $1 order by cat LOOP
|   if first is null then
| first := instance.cat;
|   end if;
|   reslt := reslt || separator || instance.cat;
|   separator := '', '';
| end loop;
| return {first,reslt};
| end;
| ' LANGUAGE 'plpgsql';
`

I also tried `... RETURNS text[] ...' and `... RETURNS text_ ...' (I
think I have read somewhere that the array type is given by basetype
plus underscore) - none of these seem to work.

I am using:

,
| cgitest=> select version();
|   version
| 
|  PostgreSQL 7.0.2 on i586-pc-linux-gnulibc1, compiled by gcc 2.95.1
| (1 row)
`

I hope you can help me; thanks in advance -

Albert.


-- 

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

[SQL] configure: what was used?

[Albert REINER]

Saluton,

I have to work with a PostgreSQL-installation on a site where I am not
the root (but I am supposed to act as the Postgres-superuser). The
system administrator installed a bunch of 7.0.2-RPMs from the
SuSE-site. How can I find out how that was configured? I am interested
mainly in locale and multibyte settings.

Thanks in advance,

Albert

(who always compiles from source and does not like nor trust YaST at all).


-- 

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

[SQL] Arrays

[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
--

[SQL] PL/pgSQL: recursion?

[Albert REINER]

Saluton,

is there any possibility to do recursion in plpgsql-functions? Here is
what I tried (I know that I can do it with a simple loop, of course; I
am just curious about what one can or cannot do in plpgsql):

,[ test-recursion ]
| drop table ttt;
| create table ttt
|   ( id int4,
| pid int4 );
| 
| insert into ttt values (1, NULL);
| insert into ttt values (2, NULL);
| insert into ttt values (3, 1);
| insert into ttt values (4, 1);
| insert into ttt values (5, 1);
| insert into ttt values (6, 2);
| insert into ttt values (7, 2);
| 
| drop function idPath(int4);
| create function idPath(int4) returns text as '
| declare
|   str text;
|   str2 text;
|   r record;
| begin
|   select id, pid into r from ttt where id = $1;
|   str := r.id::text;
|   if r.pid IS NOT NULL then
| str2 := idPath(r.id);
| str := str || '':'' || str2;
|   end if;
|   return str;
| end;' language 'plpgsql';
| 
`

And when I tried to use it, I got:

,
| select idPath(5);
| pqReadData() -- backend closed the channel unexpectedly.
| This probably means the backend terminated abnormally
| before or while processing the request.
| The connection to the server was lost. Attempting reset: Failed.
`

I'm running Postgres 7.0.2.

Thanks in advance,

Albert.


-- 

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

[SQL] Ensuring primary key is referenced at least once upon commit

[Albert REINER]

Saluton,

suppose we have PostgreSQL 7.0.2 and two tables, a and b, like:

a: id serial primary key
   ...

b: a int4 not null references a(id)
   ...

In general there will be several rows in b with the same b.a, but how
can I make sure there is AT LEAST ONE row referencing a given id from
table a?

It seems obvious to me that this can only be meaningful in a
transaction, like:

begin
insert into a ...;
insert into b (a,...) values (curr_val(a_id_seq),...);
commit

And the check can only be made before committing.

My first idea (which was not very good) was to add a table constraint
on table a similar to `... foreign key (a) references b(a) initially
deferred', because only a foreign key seems to allow checks to be
deferred, and I cannot tell from the docs whether a foreign key is
actually incompatible with a primary key declaration on the same
field. - Of course this does not work, as there is no table b by the
time a is created, or vice versa.

Looking at the documentation for CREATE TRIGGER, I do not see how to
get it to fire only just before commit - I would need a syntax like
create trigger ... before commit insert on a execute ..., which is not
what is there.

Rules - another one of those exotic things I never thought I might
actually need - do not seem to provide a solution, either.

As long as I know that nobody will mess around with the database
directly but only with scripts I provide, I can easily provide the
necessary checks etc., so this may be not so much of an issue really.
Still, it would be good to know that this works, and I am pretty sure
that this must be possible, but I seem to be looking in the wrong
direction. Any help would be appreciated.

Thanks in advance,

Albert.


-- 

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

[SQL] Support for arrays in PL/pgSQL

[Albert REINER]

Saluton,

does anyone know whether there is any support at all for arrays
in PL/pgSQL?

Thanks in advance,

Albert.


-- 

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

Re: [SQL] plpgsql language

[Albert REINER]

On Wed, Jan 24, 2001 at 01:39:48PM +0800, chard wrote:
> hello,
> i got this error when i tried to create a function 
> "unrecognized language specified in CREATE FUNCTION: 'plpgsql'" why is
> that?

man createlang

-- 

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

Re: [SQL] unreferenced primary keys: garbage collection

[Albert REINER]

> On Tue, 23 Jan 2001, Forest Wilkinson wrote:
> 
> > Jan,
> > 
> > Thanks for the reply, but your solution is rather unattractive to me.  It
> > requires that, any time a reference to an address id is changed, five
> > tables be searched for the address id.  This will create unwanted overhead

If - and I think this is the case for you - it is no problem for you
to have some superfluous adresses in your tables, but you only want to
avoid that those adresses remain there for a long time, you could
simply run the function Jan sent from a cron job. That seems to be
likely to be more efficient not only than the triggers but also to the
ON DELETE RESTRICT solution, I guess.

Trivial, but I HTH -

Albert.


> > Forest Wilkinson wrote:
> > >> > I have a database in which five separate tables may (or may not) reference
> > >> > any given row in a table of postal addresses.  I am using the primary /
> > >> > foreign key support in postgres 7 to represent these references.
> > >> >
> > >> > My problem is that, any time a reference is removed (either by deleting or
> > >> > updating a row in one of the five referencing tables), no garbage
> > >> > collection is being performed on the address table.  That is, when the
> > >> > last reference to an address record goes away, the record is not removed
> > >> > from the address table.  Over time, my database will fill up with
> > >> > abandoned address records.

-- 

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

Re: [SQL] "'" in SQL INSERT statement

[Albert REINER]

Saluton!

On Thu, Jan 25, 2001 at 11:12:34AM +0100, Markus Wagner wrote:
...
> Some of the data are strings containing the apostrophe "'" which I use
> as string delimiter.
> 
> How can I put these into my database using the INSERT statement?

I always use this sub:

#
# This sub adapted from sub TEXT of mmusic by [EMAIL PROTECTED], using
# advice from the pgsql-novice mailing list (David Rugge, 1 Aug 1999).
#
# Do the necessary quoting for strings.
#
sub stdstr {
  my $or = $_[0];
  return undef unless (defined($or));
  $or =~ s /\\//g;# mmusic doesn't have this, nor does D. Rugge.
  $or =~ s /\'/\\\'/g;
  $or =~ s /\"/\\\"/g;
  $or =~ s /%/\\%/g;  # mmusic doesn't have this.
  return $or;
}

Obviously you also need to escape the \. I no longer remember why "
and % are needed, but I certainly did have some reason then.

Albert.


-- 

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

[SQL] 7.0.2-docs: textpos -> strpos

[Albert REINER]

Saluton,

in the 7.0.2-docs I find the function textpos:

,
|Table 5-5. String Functions
|   ...
|textpos(text,text)  text  locate  specified  substring
|position('high','ig')
`

However, in psql it seems one has to use strpos:

,
| litdb=> select textpos('a  b', '  ');
| ERROR:  Function 'textpos(unknown, unknown)' does not exist
| Unable to identify a function that satisfies the given argument types
| You may need to add explicit typecasts
| litdb=> select strpos('a  b', '  ');
|  strpos
| 
|   2
| (1 row)
| 
| litdb=> select version();
|   version
| 
|  PostgreSQL 7.0.2 on i586-pc-linux-gnulibc1, compiled by gcc 2.95.1
| (1 row)
`

textpos does not seem to exist:

,
| litdb=> \df .*pos
|  List of functions
|  Result |Function |   Arguments
| +-+
|  int4   | position| text text
|  float8 | positionjoinsel | oid oid int2 oid int2
|  float8 | positionsel | oid oid int2 - int4
|  int4   | strpos  | text text
| (4 rows)
`

Maybe this should be fixed in the docs.

Albert.


-- 

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

[SQL] Suggestion for psql: --file -

[Albert REINER]

Saluton,

wouldn't it be a good idea (and if it is, I am not sure what list to
post it to) to have psql's option -f / --file take "-" for stdin, as
many programs do?

In shell scripts this would facilitate using here-documents:

cat <

Re: [SQL] Automated scripting...

[Albert REINER]

Saluton,

On Tue, Jan 30, 2001 at 03:54:53PM -0800, [EMAIL PROTECTED] wrote:
...
> I'm building a script to create a relatively large database.  At some
> point in the script I would like to be able to save values into
> variables so that I can use them to populate rows.

Your script will be handled via psql, right? Then you can use \set and
interpolation, as described in `man psql`.

Albert.


-- 

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

[SQL] PL/pgSQL: possible parsing or documentation bug?

[Albert REINER]

Saluton,

plpgsql seems to get confused when using variable names that coincide
with, e.g., column names:

create Function IdOfPossiblyNewAuthor(text) returns int4 as '
  declare
id int4;
  begin
select id into id from author where name = $1;
raise notice ''ID found: %'', id;
if id is null then
  insert into author (name) values ($1);
  select currval(''author_id_seq'') into id;
  raise debug ''Author inserted. ID: %'', id;
end if;
return id;
  end;
' language 'plpgsql' with (IsCachable);

Logically it is clear which "id" should be parsed as the variable,
which as author.id, but I have to use a different name to make this
work. I do not see any mention on this restriction of variable names
(the full extent of which I do not know) in the documentation (7.0.2).

Albert.


-- 

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

Re: [SQL] how to create this trigger?

[Albert REINER]

Don't know much, but couldn't you let increment_value take an argument
and run it on new.code?

But maybe I am completely off.

Albert.


On Thu, Feb 01, 2001 at 04:48:28PM -0500, Ramiro Arenas Ramírez wrote:
> I need to create a trigger that increment a value in a column 
> of table1 where a row is inserted in table 2
> 
> I have tried whit this but it just does nothing.
> 
> CREATE FUNCTION increment_value () RETURNS opaque AS 
> 'DECLARE
>code int4;
>BEGIN
>code := new.code;
>UPDATE table1 
>SET value = value + 1
>WHERE id = code;
> RETURN NEW;
> END;'  LANGUAGE 'plpgsql';
> 
> CREATE TRIGGER insert_on_table2 BEFORE INSERT ON table2 
>   FOR EACH ROW EXECUTE PROCEDURE increment_value();
> 
> 
> Can you help me? 
> 
> 
> 
> 

-- 

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

Re: [SQL] Suggestion for psql: --file -

[Albert REINER]

On Fri, Feb 02, 2001 at 04:16:05PM +0100, Peter Eisentraut wrote:
> Albert REINER writes:
...
> > P.S: BTW, the man page (7.0.2) of psql is not very clear: it took me
> > some experimentation to find out that you have to do "\set VARIABLE"
> > interactively or give "--set VARIABLE=" to set a variable that does
> > not take a value.
> 
> Suggested new wording?

What about:

-v, --variable, --set variable[=[value]]

Performs a variable assignment, like the \set internal command.  Note
that you must separate name and value, if any, by an equal sign on the
command line. To unset a variable, leave off the equal sign and the
value.  To just set a variable without a value, use the equal sign but
leave off the value. These assignments are done during a very early
state of startup, so variables reserved for internal purposes might
get overwritten later.


Albert.

-- 

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

Re: [SQL] How to modify type in table?

[Albert REINER]

Saluton,

On Tue, Jan 30, 2001 at 10:22:23AM +0700, Jaruwan Laongmal wrote:
> Dear Sir.
> Would you like to inform me how to modify type in table?
> For example , I define type as varchar(14) , but I want to modify to varchar(120). 
>How to do this.

If it is not too much data, the easiest would be to use pg_dump to
dump the database (or just the table), edit the definition, and feed
the changed file to psql in order to get the data back. Or you can
create a different table with the correct structure, copy the data
there, drop the old table, rename the new one to the correct one.

Albert.


-- 

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

Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?

[Albert REINER]

On Wed, Feb 07, 2001 at 10:38:53AM -0600, Brice Ruth wrote:
> Is there a simple (unix) command I can run on text files to convert
> cr/lf to lf?  The way I did it seemed pretty ass-backward to me (not to
> mention time consuming).
> 
> -Brice

On many systems (linux at least) there is a command dos2unix, which is
actually an alias for `recode ibmpc:`. But that will take care of more
than just CR, e.g. umlauts, diacritics, etc..

Albert.


-- 

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

Re: [SQL] fetching the id of a new row

[Albert REINER]

On Thu, Feb 08, 2001 at 05:28:59PM -0500, Jelle Ouwerkerk wrote:
> Hi,
> 
> How might I insert a new row into a table and return the id of the new row
> all in the same SQL statement? The id is generated by a sequence. Up to
> now I've been getting the nextval of the sequence first and then inserting
> with the id in a second SQL exec. Is there a faster way (in a general
> case, without writing SQL or plpgsql functions)?
> 
> Thanks

I do not know of a way to insert and select in one statement without
the use of a function (what's the problem with those, by the way?),
but as far as I can tell nextval() will return the next value for any
backend, so if you have more than one backend inserting at the same
time you might end up inserting with the same id twice. Instead you
should insert once, without specifying the id (so that the default
value, which must be set to nextval()) will be used; to obtain the id,
if indeed you need it, you can than select currval(), which is
guaranteed to work on a per-backend basis.

Albert.


-- 

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

[SQL] Historical dates in Timestamp

[Albert REINER]

Saluton,

I have a database with dates, some of which are historical dates.
When I wanted to enter May 28th, 812 I got an error message, had to
use 0812 for the year instead:

albert=> CREATE DATABASE test;
CREATE DATABASE
albert=> \c test
You are now connected to database test.
test=> CREATE TABLE dates (d timestamp);
CREATE
test=> insert into dates (d) values ('812-5-28');
ERROR:  Bad timestamp external representation '812-5-28'
test=> insert into dates (d) values ('0812-5-28');
INSERT 81801 1

The same happens for BC dates:

test=> insert into dates (d) values ('812-5-28 BC');
ERROR:  Bad timestamp external representation '812-5-28 BC'
test=> insert into dates (d) values ('0812-5-28 BC');
INSERT 81802 1

Is it really reasonable to enforce that the number of years is four
digits at least?

I'm running:

test=> SELECT version();
  version

 PostgreSQL 7.0.2 on i586-pc-linux-gnulibc1, compiled by gcc 2.95.1
(1 row)

Bye,

Albert.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [SQL] Re: select substr???

[Albert REINER]

On Mon, Apr 09, 2001 at 06:05:55PM +0100, Tim Johnson wrote:
> Hi,
> 
> I have postgres 6.x (where x is something).
> 
> I have the following list of data
> 
> data
> 
> ABC*
> ABC
> ABC-
...
> what I want to do is 'select distinct(data) [ignoring non alphanumeric
> characters] order by data'

somewhere I use the following, which might be adapted to do what you
want.  I am sure there are more elegant ways of doing this, though.

 create function ComparisonString(text) returns text  as '
  declare
t text;
r text;
c char;
ns bool;
  begin
if $1 is null then
  return NULL;
end if;
t = lower(trim(both $1));
r = ;
ns = false;
for i in 1 .. char_length(t) loop
  c = substring(t from i for 1);
  if c = '' '' then
if ns then
  r = r || '' '';
end if;
ns = false;
  else
if position(c in ''abcdefghijklmnopqrstuvwxyz0123456789'') > 0 then
  r = r || c;
  ns = true;
end if;
  end if;
end loop;
return trim(both r);
  end;
' language 'plpgsql' with (IsCachable);

Albert.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [SQL] RE: Re: select substr???

[Albert REINER]

Thanks, interesting. I did not find any mention of regular expressions
in the 7.0.2 docs I installed locally.

BTW, your code does not do exactly the same, as it removes any
whitespace while the other one only collapses consecutive blanks. But,
of course, regular expressions in PL/pgSQL make this much easier.

As a further aside, I think that in both versions of the function the
check for `$1 IS NULL' is not necessary; I got the impression that
passing NULL as an argument to a function will automatically return
NULL as the result, doesn't it?

Albert.


On Tue, Apr 10, 2001 at 05:41:26PM -0500, Jeff Eckermann wrote:
> Regular expressions make this much easier.  The below could be shortened to:
> 
>   create function ComparisonString(text) returns text  as '
> declare
>   t alias for $1;
>   r text;
>   c char;
>   begin   
>   if t is null  or t !~ ''[^a-zA-Z0-9]''
>  then
> return t;
>   end if;
>   r = '''';
>   for i in 1 .. char_length(t) loop
> c = substring(t from i for 1);
> if c ~ ''[a-zA-Z0-9]''
> then
> r = r || c;
> end if;
>   end loop;
>       return r;
> end;
>   ' language 'plpgsql' with (IsCachable);
> 
> > -Original Message-
> > From:   Albert REINER [SMTP:[EMAIL PROTECTED]]
...
> > 
> > somewhere I use the following, which might be adapted to do what you
> > want.  I am sure there are more elegant ways of doing this, though.
> > 
> >  create function ComparisonString(text) returns text  as '
> >   declare
> > t text;
> > r text;
> > c char;
> > ns bool;
> >   begin
> > if $1 is null then
> >   return NULL;
> > end if;
> > t = lower(trim(both $1));
> > r = '''';
> > ns = false;
> > for i in 1 .. char_length(t) loop
> >   c = substring(t from i for 1);
> >   if c = '' '' then
> > if ns then
> >   r = r || '' '';
> > end if;
> > ns = false;
> >   else
> > if position(c in ''abcdefghijklmnopqrstuvwxyz0123456789'') > 0
> > then
> >   r = r || c;
> >   ns = true;
> > end if;
> >   end if;
> > end loop;
> > return trim(both r);
> >   end;
> > ' language 'plpgsql' with (IsCachable);
> > 

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

[SQL] RULE ... TO table.column

[Albert REINER]

Saluton,

I am using

,
|  SELECT version();
|   version
| 
|  PostgreSQL 7.0.2 on i586-pc-linux-gnulibc1, compiled by gcc 2.95.1
| (1 row)
`

, and upgrading to 7.1 is not possible (it was hard enough to get the
admin to upgrade from 6.5.3).

While the docs say that I can create a rule on table.column, I get the
error message:

,
| litdb=> create rule no_update_on_journal_title__rl as
| litdb->   on update to journal.title do instead nothing;
| ERROR:  attribute level rules currently not supported
`

Am I doing something wrong here, or do I have to work around this via
... where old.title = new.title ... (would this work?), or should I
use a before-trigger?

Are ``attribute level rules'' supported in 7.1 (so I can at least put
a note into the code)?

Thanks in advance,

Albert.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] '13 months ago'::reltime

[Albert REINER]

Saluton,

I do not quite understand the following:

,
| albert=> select '13 months ago'::reltime;
|   ?column?
| -
|  1 year 25 00:00 ago
| (1 row)
| 
| albert=> select '13 months ago'::interval;
| ?column?
| 
|  1 year 1 mon 00:00 ago
| (1 row)
| 
| albert=> select version();
|   version
| 
|  PostgreSQL 7.0.2 on i586-pc-linux-gnulibc1, compiled by gcc 2.95.1
| (1 row)
`

Shouldn't 13 months be 1 year and 1 month even for reltime?

Somewhat puzzled,

Albert.


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] create table

[Albert REINER]

On Sun, Apr 29, 2001 at 09:34:29PM +0200, LeoDeBeo wrote:
> can anybody explain me the syntax of Create Table documentation??
...
> i also don't understand what the [ ... ] and [, ... ] means. I do know that 
> brackets denote options and | alternatives.

I guess:

 ``[ ... ]'' means that you may repeat the clause, and
``[, ... ]'' means that you may repeat the clause, using a comma for separation

Albert.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [SQL] How to execute a system file in procedure?

[Albert REINER]

On Mon, Aug 20, 2001 at 10:08:36AM -0400, Raymond Chui wrote:
> I would like execute a system command or my program or my shell
> script in procedure. How do I do that?

use .

Albert.


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] Re: [NOVICE] protected ON DELETE CASCADE

[Albert Reiner]

On Thu, 23 Aug 2001, Murray Hobbs wrote:

> A <- B
> A <- C <- D
> 
> i want to maintain integrity so that if A is deleted from then so is
> anything referencing from B and C - no problem ON DELETE CASCADE
> 
> but if there are any D's that point back to A (through composite key in
> C) i don't want the delete to go ahead - at all - i want an error
> message and condition

what about ON DELETE RESTRICT there?

> I've looked at TRIGGER but then i'm writing a function (in SQL?) which
> is called by a trigger - again - how do i confirm or reject a delete?

Look at the docs: there is a difference between DO and DO INSTEAD (I
think, I am no expert on Pg, and I do not have access to a Pg system right
now).

Albert.



---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl