[SQL] Using a rule as a trigger.
Hi all,
I have looked at some previous posting and thought that I had found
exactly what I need. What I need is to insert an id (from a sequence)
when a new record is inserted. The following example almost does what I
need :
CREATE TABLE topics (id int, topic varchar(50), descriotion text);
CREATE SEQUENCE nextid start 1;
CREATE RULE ins_topic AS ON INSERT TO topics WHERE id ISNULL DO UPDATE
topics SET id=nextval('nextid') WHERE id ISNULL;
This example updates the last insert. I need it to update the currnet
insert. How do I do this?
Kind regards
Andrew Higgs
Re: [SQL] Using a rule as a trigger.
Andrew Higgs wrote:
>
> Hi all,
>
> I have looked at some previous posting and thought that I had found
> exactly what I need. What I need is to insert an id (from a sequence)
> when a new record is inserted. The following example almost does what I
> need :
>
> CREATE TABLE topics (id int, topic varchar(50), descriotion text);
> CREATE SEQUENCE nextid start 1;
> CREATE RULE ins_topic AS ON INSERT TO topics WHERE id ISNULL DO UPDATE
> topics SET id=nextval('nextid') WHERE id ISNULL;
>
> This example updates the last insert. I need it to update the currnet
> insert. How do I do this?
>
> Kind regards
> Andrew Higgs
Isn't better create the field "id" as "serial" instead "int" ???
this will do what you need.
--
==
AKACIA TECNOLOGIA
Desenvolvimento de sistemas para Internet
www.akacia.com.br
Re: [SQL] Possible bug? WAS :Bad (null) varchar() externalrepresentation.
On Thu, 11 Jan 2001, Justin Clift wrote:
> I haven't seen a mention of a maximum number of constraints of similar
> applying to a table. If so, then could someone please point me to it...
>
> The reason I mention this is because I've found what seems to be causing
> this problem I'm experiencing with Postgres 7.03 :
>
> CREATE TABLE "staff_details" (
> "userid" character varying(24) NOT NULL,
> "password" character(13) NOT NULL,
> "name" character varying(96) NOT NULL,
> "role" int2 NOT NULL,
> "dob" date NOT NULL,
> "phone_one" character varying(14) NOT NULL,
> "phone_two" character varying(14),
> "phone_three" character varying(14),
> "address" character varying(280),
> "status" int2,
> "managers_notes" character varying(600),
> CONSTRAINT "staff_details_uesrid" CHECK ((length(userid) < 25)),
> CONSTRAINT "staff_details_password" CHECK ((length("password") <
> 14)),
> CONSTRAINT "staff_details_name" CHECK ((length(name) < 97)),
> CONSTRAINT "staff_details_dob" CHECK
> (date_ge(date(("timestamp"('2001-01-08'::date) - '18 years
> 00:00'::"interval")), dob)),
> CONSTRAINT "staff_details_phone_one" CHECK ((length(phone_one) <
> 17)),
> CONSTRAINT "staff_details_phone_two" CHECK ((length(phone_two) <
> 17)),
> CONSTRAINT "staff_details_phone_three" CHECK
> ((length(phone_three) < 17)),
> CONSTRAINT "staff_details_address" CHECK ((length(address) <
> 281)),
> CONSTRAINT "staff_details_managers_notes" CHECK
> ((length(managers_notes) < 601)),
> PRIMARY KEY ("userid")
> );
>
> When I attempt to insert data into this table, I get the following error
> :
>
> foobar=# insert into staff_details values ('',
> encrypt('foo'), 'Joshua', 1, '1970-07-01', '(03) 9867 5432', '(041) 309
> 2819', NULL, '1 Blankety-Blank Way\nBazzville', NULL, NULL);
>
Current source seem to insert fine (don't have a 7.0 system to test on
anymore). Also, aren't alot of these length checks meaningless? I think
the values are converted to the correct type first, so the phone number
really shouldn't possibly be longer than 16 since it's a varchar(14).
Re: [SQL] Using a rule as a trigger.
As someone else said a serial is probably easier, but
a trigger is probably a better bet than a rule for this purpose.
Using a plpgsql before insert trigger will do it.
On Wed, 10 Jan 2001, Andrew Higgs wrote:
> Hi all,
>
> I have looked at some previous posting and thought that I had found
> exactly what I need. What I need is to insert an id (from a sequence)
> when a new record is inserted. The following example almost does what I
> need :
>
> CREATE TABLE topics (id int, topic varchar(50), descriotion text);
> CREATE SEQUENCE nextid start 1;
> CREATE RULE ins_topic AS ON INSERT TO topics WHERE id ISNULL DO UPDATE
> topics SET id=nextval('nextid') WHERE id ISNULL;
>
>
> This example updates the last insert. I need it to update the currnet
> insert. How do I do this?
[SQL] connecting to postgres server from Access
Hi,
I'm running a postgres db server on my linux machine. Someone else would like
to build his frontends with MS Access and use the postgres server as backend.
He installed the Pg/ODBC-Driver linked to at the pg web site. But when he
tries to link in some table he always gets an error message about wrong
authentication. I inserted his IP address in the pg_hba.conf file and created
a linux user account for him as well as a pg user account within "template1".
So he could log in and use "createdb" to create his own db. I tried to set
his password within pgaccess, but pgaccess claims about wrong input ("").
Leaving the password field empty doesn't help to get the connection. Then I
set his password with "ALTER USER" in pgsql. But there still is no connection
from access to pg yet.
Could someone help?
Thanks,
Markus
Re: [SQL] connecting to postgres server from Access
On Wed, 10 Jan 2001, Markus Wagner wrote:
> Hi,
>
> I'm running a postgres db server on my linux machine. Someone else would like
> to build his frontends with MS Access and use the postgres server as backend.
> He installed the Pg/ODBC-Driver linked to at the pg web site. But when he
> tries to link in some table he always gets an error message about wrong
> authentication. I inserted his IP address in the pg_hba.conf file and created
> a linux user account for him as well as a pg user account within "template1".
> So he could log in and use "createdb" to create his own db. I tried to set
> his password within pgaccess, but pgaccess claims about wrong input ("").
> Leaving the password field empty doesn't help to get the connection. Then I
> set his password with "ALTER USER" in pgsql. But there still is no connection
> from access to pg yet.
First of all, questions about interfacing to PostgreSQL are better
directed to pgsql-interfaces list. You might not get much help posting to
the sql list.
What kind of authorization are you using in pg_hba.conf? (can you post the
line from pg_hba.conf for him)
What error message does he get in Access?
Can he connect to the database from another Linux/Unix machine via
psql? (or, from his Windows machine using a PostgreSQL-compatible command
interface, like isql)?
There's a FAQ on PostgreSQL + Access at www.scw.org/pgaccess. It may help
as well.
Good luck,
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
[SQL] Table locking.
Hi. I've always had trouble understanding how to properly deal with the following case. I need to run an insert on a table but I don't want any other transactions to commit the insert before me. Within the logic of the program, I run a select to see if there is a value there, then the insert. Between these 2 statements someone could commit one that causes the insert to fail. Is there a better way to do this than locking the table in SHARE ROW EXCLUSIVE MODE and trying to be certain that anything else that may call an insert there asks for the same type of lock? -Michael _ http://fastmail.ca/ - Fast Free Web Email for Canadians
Re: [HACKERS] Re: [SQL] PostgreSQL web page
On Tue, 9 Jan 2001, The Hermit Hacker wrote: > On Tue, 9 Jan 2001, Kaare Rasmussen wrote: > > > I wonder why http://www.postgresql.org is not updated with news. The > > latest "Latest News" is from Nov 16. I have to go to > > http://www.pgsql.com to find some interesting news later than that: > > 2000-12-22 PostgreSQL, Inc. Releases Open Source Replication & Database > > and2000-12-12 2000 Linux Journal Editor's > > Choice Award for Best Database > > > > In fact I was looking for the Replication server. No mention seems to be > > available on postgresql.org. > > > > Also the mailing list archive for pgsql-sql is not listed here: > > http://www.postgresql.org/devel-corner/index.html - but you can see it > > if you write the correct URL yourself. > > I have no graphical browser right now, and lynx isn't installed, so I'm a > bit at a disadvantage here ... but, pgsql-sql *should* be listed under > users-gallery, not devel ... devel is -hackers, -bugs, -ports, etc ... Unless the clock on hub was wrong, I have the award happening on 11/16/Y2K. The replication server info was a press release by PostgreSQL, Inc. Not the PostgreSQL Global Development Group. Between GB and PG,Inc with the constant barrage of press releases I made the decision months ago that NO press releases from ANY group would be posted on the PostgreSQL website - these groups have their own websites for that and PostgreSQL.org doesn't need to be caught up in the middle. pgsql-sql is in the users section - both info and archives. > Beyond that, the web site is maintained by Vince ... but I thought the > Award was definitely up there already ... Both on the main page and in latest news. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com ==
