[SQL] SOME PL/PGSQL PROBLEMS

I found there are some problems with PL/pgSQL. 1) 'Drop Table' doesn't work at all 2) '--' comment causes parsing error 3) Execute doesn't work at all. It always causes parsing error! Some questions 1) Can I use variable in FROM clause for a table name? 2) Can I use variable in WHERE such as xxx

[SQL] Btree index on varchar

Hello, I have a question for Btree indexes on a varchar field. Does postgres (7.0.2) uses a special encoding when creating Btree indexes on varchar?? Is it much more efficient to create indexes on integer than on strings with average length 30 chars?? Thank you in advance for your help Regard

Re: [SQL] creating "job numbers"

Jan, Thanks, I must be missing something here. Bear with me, I am trying to form an intelligent question. Using the serial data type... I don't understand when the backend would skip a number. If the db is assigning the number with the insert, then if two (or more) clients are trying to ins

Re: [SQL] Foreign key referencing subclasses.

>> CREATE TABLE resource_record( >> rrid SERIAL >> -- etc. >> ); > There is no primary key for this table. Just write PRIMARY KEY after > SERIAL. There is. I accidentally left it out in the post. > CREATE INDEX soa_record_pkey ON soa_record ( rrid ); > You could also state the referenced f

Re: [SQL] SOME PL/PGSQL PROBLEMS

On 3/23/01, 9:10:16 AM, datactrl <[EMAIL PROTECTED]> wrote regarding [SQL] SOME PL/PGSQL PROBLEMS: Sorry, can't reproduce this at all Jack > I found there are some problems with PL/pgSQL. > 1) 'Drop Table' doesn't work at all No problems in 7.1, can't remember about previous versions > 2) '--

RE: [SQL] creating "job numbers"

Thanks Micheal, I guess what I am trying to figure out is, 'how important is this in reality, in a working setup'. I know that at times I want things to be perfect, and it is not necessary. I was thinking about how I do this manually. I would take a sheet of paper, write numbers down the le

Re: [SQL] creating "job numbers"

"postgresql" <[EMAIL PROTECTED]> writes: > Using the serial data type... I don't understand when the backend > would skip a number. The value returned by a nextval() call will not be returned again by other nextval() calls, even if the surrounding transaction is later rolled back. Agreed, this

Re: [SQL] Btree index on varchar

Alexaki Sofia <[EMAIL PROTECTED]> writes: > I have a question for Btree indexes on a varchar field. > Does postgres (7.0.2) uses a special encoding when creating > Btree indexes on varchar?? "Special encoding"? No ... a varchar is a varchar. > Is it much more efficient to create indexes on int

Re: [SQL] drop table in PL/pgSQL

You cannot CREATE|DROP ALTER table in PL/pgSQL, in general, plsql can only take DML(i.e. SELECT| INSERT|UPDATE..) Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 23 Mar

Re: [SQL] creating "job numbers"

postgresql wrote: > Jan, > > Thanks, I must be missing something here. Bear with me, I am > trying to form an intelligent question. > > Using the serial data type... I don't understand when the backend > would skip a number. > If the db is assigning the number with the insert, then if two (or > mo

Re: [SQL] SOME PL/PGSQL PROBLEMS

You blame something should not be blamed. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 23 Mar 2001, datactrl wrote: > I found there are some problems with PL/pgSQL. > 1

Re: [SQL] trigger output to a file

pgsql-sql wrote: > Hello Everyone, > > Here's my simple question. > > I just want to know/get the recent changes made to a table. > Deeper? I wanted the Postgresql server to *publish* every > changes made to a table (similar to replication, incremental transfer, > etc.). > What is the best way to

Re: [SQL] drop table in PL/pgSQL

On Fri, Mar 23, 2001 at 09:52:56AM -0800, Jie Liang wrote: > > You cannot CREATE|DROP ALTER table in PL/pgSQL, > in general, plsql can only take DML(i.e. SELECT| > INSERT|UPDATE..) You can't? I just did (on PG 7.1). AFAIK, you _can_ CREATE/DROP, but you can't roll back.

Re: [SQL] drop table in PL/pgSQL

Hmm, I didn't know that, this general idea from Orcale plsql, So, I assume that you can SELECT somefield into a_new_table FROM a_old_table in pg 7.1??? Thank you. No DDL can be roll back. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)3

Re: [SQL] trigger output to a file

I haven't given this a lot of thought, so take it with a grain of salt. But my feeling is that publishing such a detailed log isn't the most effective way to do this sort of thing. How about, instead, changing the structure of your database to keep "old" information? Consider, for example, a simp

[SQL] how do I check if a temporary table exists?

How do I check if a temporary table exists? Searching pg_tables with a temporary table name we chose always fails. JACK ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAI

[SQL] Serials.

Please see below for my table schema. I have two questions. (1) Why is a sequence limited to 2147483647, it seems very small? (2) If I reset the sequence, then try another insert. It will not insert anything until it cycles through all sequences and finds an unused one. It will give the followin