[SQL] database log
Since "raise exception" in pl/pgSQL will write error message to database log, how can I read database log. If I do "select * from pg_log;", it will show an error message as "pg_log can not be accessed by user". JACK ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Re: psql win32 version
Hi, Christopher Thank you for your help, but the URL http://www.zeos.dn.ua/download/ipgsql-1.6.2.zip is a broken link. Would you please check again. Thank you very much. Jack - Original Message - From: "Christopher Sawtell" <[EMAIL PROTECTED]> Newsgroups: comp.databases.postgresql.sql Sent: Thursday, March 15, 2001 1:19 PM Subject: Re: [SQL] Re: psql win32 version > This one worked a minute ago:- > > http://www.zeos.dn.ua/download/ipgsql-1.6.2.zip > > > On Thu, 15 Mar 2001 14:14, datactrl wrote: > > The URL shown below can not get through. Would you please check again. > > > -- > Sincerely etc., > > NAME Christopher Sawtell > CELL PHONE 021 257 4451 > ICQ UIN45863470 > EMAIL csawtell @ xtra . co . nz > CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz > > -->> Please refrain from using HTML or WORD attachments in e-mails to me > <<-- > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] creating "job numbers"
I have been working with PG for about 2 months now. I am creating a job tracking system for my company. I have written a front end on the workstations (all macintoshes) that seems to be working quite well. However, I have a problem with a concept. In my current setup I have only one workstation that is actually inputting new jobs. So, I took the expedient way to create the job number. Ask PG to count the rows, add a magic number and insert this data. This all happens in one connection. What are the odds of two people hitting the db at the same time? In the current set up nil. There is only one entry computer. I want to change the system to use a job number generated by PG. I created a test table and I am playing with inserting and the sequence function works great. However, I am at a loss of how to pick up this next (last) job. I have read the docs and I still am confused. I can not first ask with the number will be, and asking for the previous oid after the fact can also lead to the same problem. so that leaves me with, 1 ask for that last oid from this workstation ip, or 2 since a job is inserted with data, I could do a select of this data after the insert (not very elegant). How are you professionals handling this problem? I like the ability to insert and have the system give me the number. As I grow into more workstations inputting the jobs I won't have to worry about chasing the next highest number. Thanks, Ted P. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: creating "job numbers"
postgresql <[EMAIL PROTECTED]> wrote: >I can not first ask with the number will be, and asking for the previous >oid after the fact can also lead to the same problem. If you use sequences and the currval() and nextval() functions, you can. See e.g. http://www.postgresql.org/docs/aw_pgsql_book/node85.html . HTH, Ray -- "a infinite number of monkeys typing into GNU emacs would never make a good program" .../linux/Documentation/CodingStyle ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] creating "job numbers"
Check out nextval() and currval(). They do exactly what you need. They're
also specific to the current backend, so you can guarantee that the same
value won't be passed to two different frontend sessions.
nextval('sequencename') -> the number that will be assigned next in
the current backend; and
currval('sequencename') -> the number that was last assigned in the
current backend (undefined if there's been
no INSERT in this session)
Hope this helps.
--
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
(Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
[EMAIL PROTECTED] - http://www.unc.edu/~aperrin
On Thu, 22 Mar 2001, postgresql wrote:
> I have been working with PG for about 2 months now. I am creating a
> job tracking system for my company. I have written a front end on the
> workstations (all macintoshes) that seems to be working quite well.
> However, I have a problem with a concept.
>
> In my current setup I have only one workstation that is actually
> inputting new jobs. So, I took the expedient way to create the job
> number. Ask PG to count the rows, add a magic number and insert
> this data. This all happens in one connection. What are the odds of
> two people hitting the db at the same time? In the current set up nil.
> There is only one entry computer. I want to change the system to use
> a job number generated by PG. I created a test table and I am
> playing with inserting and the sequence function works great.
> However, I am at a loss of how to pick up this next (last) job. I have
> read the docs and I still am confused. I can not first ask with the
> number will be, and asking for the previous oid after the fact can
> also lead to the same problem. so that leaves me with, 1 ask for
> that last oid from this workstation ip, or 2 since a job is inserted with
> data, I could do a select of this data after the insert (not very elegant).
>
> How are you professionals handling this problem? I like the ability to
> insert and have the system give me the number. As I grow into more
> workstations inputting the jobs I won't have to worry about chasing
> the next highest number.
>
> Thanks,
> Ted P.
>
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] how to build this string ?
Hello there Is it possible (and I think it is) to do the following : I have a table with diameters and types. I need to build a comma separated string. typ diam 01800 01840 01870 011120 02760 02780 02800 02900 031200 031234 031352 select diam from zylinder where typ='01' should produce the string "800,840,870,1120" Thanks for any help ... jr PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland phone: +4141 790 4040 fax: +4141 790 2545 mobile: +4179 211 0315 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] creating "job numbers"
From: "postgresql" <[EMAIL PROTECTED]>
> In my current setup I have only one workstation that is actually
> inputting new jobs. So, I took the expedient way to create the job
> number. Ask PG to count the rows, add a magic number and insert
> this data. This all happens in one connection. What are the odds of
> two people hitting the db at the same time? In the current set up nil.
> There is only one entry computer. I want to change the system to use
> a job number generated by PG. I created a test table and I am
> playing with inserting and the sequence function works great.
> However, I am at a loss of how to pick up this next (last) job. I have
> read the docs and I still am confused. I can not first ask with the
> number will be, and asking for the previous oid after the fact can
> also lead to the same problem. so that leaves me with, 1 ask for
> that last oid from this workstation ip, or 2 since a job is inserted with
> data, I could do a select of this data after the insert (not very
elegant).
I wouldn't use oid's for this - create a jobnum field and use a sequence.
Sequences are smarter than you think, use:
select currval('mysequence') to get the current value and
select nextval('mysequence') to get the next value *for this backend*
So - each client will be guaranteed a unique number. Note that if you "use
up" a number and e.g. an insert fails there will be gaps in your numbering.
Also check out the SERIAL data-type which can provide automatic numbering
for the fields.
I'm sure there are examples in Bruce's book (there's a link on
www.postgresql.org)
- Richard Huxton
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[SQL] Foreign key referencing subclasses.
Hi, it seems I can't have a foreign key that references some subclass. Postgres says it can't figure out what its primary key is. The primary key is defined in the superclass. I have something like this: CREATE TABLE resource_record( rrid SERIAL -- etc. ); CREATE TABLE soa_record( -- blah, blah ) INHERITS(resource_record); CREATE TABLE domain( -- ... soaid REFERENCES soa_record -- * ); * = This doesn't work. I can reference resource_record here, but I can not reference soa_record that way. Referencing resource_record* doesn't work either. -- johs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Birthday search.
I have a table with personal data, including date of birth in date format. What i need is a select that searches birthdays in certain range. E.g. I need to find all birthdays starting from 15.april until 20th december. As you all understand, year doesn't matter :P Any simple and elegant solutions to it? Greetings, Antti ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] how to build this string ?
From: <[EMAIL PROTECTED]> > Hello there Hello again Juerg - I take it you got that monster query working after? > Is it possible (and I think it is) to do the following : > > I have a table with diameters and types. I need to build a comma separated > string. > > typ diam > 01800 > 01840 > 01870 > 011120 > > select diam from zylinder where typ='01' > > should produce the string "800,840,870,1120" You'll want to build yourself a custom aggregate function. Check the mail archives for someone else who did this recently (in the last month or so, Tom Lane was involved in the discussion too). I forget the fella's name, but he should have almost exactly what you want. You can then do something like: select typ, commify(diam) from zylinder group by typ; - Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Foreign key referencing subclasses.
Hi Johannes, On Thursday, 22. March 2001 15:18, Johannes Grødem wrote: > Hi, > > it seems I can't have a foreign key that references some subclass. > Postgres says it can't figure out what its primary key is. The > primary key is defined in the superclass. > > I have something like this: > > CREATE TABLE resource_record( > rrid SERIAL > -- etc. > ); There is no primary key for this table. Just write PRIMARY KEY after SERIAL. > CREATE TABLE soa_record( > -- blah, blah > ) INHERITS(resource_record); > > CREATE TABLE domain( > -- ... > soaid REFERENCES soa_record -- * > ); > > > * = This doesn't work. I can reference resource_record here, but I > can not reference soa_record that way. Referencing resource_record* > doesn't work either. Primary keys as well as other indexes are not inherited, unfortunately. You should create the indexes for any subclass manually, like: CREATE INDEX soa_record_pkey ON soa_record ( rrid ); You could also state the referenced field name (you actually have to, if you happen to reference to a non-primary key field): CREATE TABLE ... soaid int4 REFERENCES soa_record ( rrid ) Note that SERIAL is actually int4 with automagically created sequences. You can't leave out the column's type for references. And note also that referencing to a superclass* (including subclasses) does not work for 7.0.x. I think it changes with 7.1 but I'm not sure. You could create your own triggers, though. Hope that helps, Christof. -- gl.aser . software engineering . internet service http://gl.aser.de/ . Planckstraße 7 . D-39104 Magdeburg Tel. +49.391.7 44 77 10 . Fax +49.391.7 44 77 13 . Mobil 0177.77 92 84 3 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Birthday search.
Here's an example of something that works with some of
the data that I have lying around. Replace exit_dt
with "birthday" (or whatever you have named the field
that holds the birthday information) and replace
ticket_no with name (or whatever) and you should be on
the right track.
SELECT exit_dt, ticket_no FROM tscales
WHERE (
date_part('month', exit_dt) > 4 AND
date_part('month', exit_dt) < 12)
OR (
date_part('month', exit_dt) = 4 AND
date_part('day', exit_dt) >= 15
)
OR (
date_part('month', exit_dt) = 12 AND
date_part('day', exit_dt) <= 20)
ORDER BY exit_dt;
I don't consider myself enough of a SQL guru to know
if this is the "correct" way to do this, but this
query selects some 21000 records out of a 12
record table on my desktop test machine (PII 400 96M
Ram standard IDE hard drive running Linux) without any
indexes on exit_dt in a couple of seconds.
For more information check out the user guide section
on date/time functions. It is quite interesting. The
extract function would have served as well, but it
requires more typing.
Jason
--- Antti Linno <[EMAIL PROTECTED]> wrote:
> I have a table with personal data, including date of
> birth in date format.
> What i need is a select that searches birthdays in
> certain range.
> E.g. I need to find all birthdays starting from
> 15.april until 20th
> december. As you all understand, year doesn't matter
> :P
> Any simple and elegant solutions to it?
>
> Greetings,
> Antti
>
>
>
> ---(end of
> broadcast)---
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED]
> so that your
> message can get through to the mailing list cleanly
__
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Foreign key referencing subclasses.
On Thu, 22 Mar 2001, [iso-8859-1] Johannes Grødem wrote: > Hi, > > it seems I can't have a foreign key that references some subclass. Postgres > says it can't figure out what its primary key is. The primary key is defined > in the superclass. Unique/primary key doesn't inherit to subclasses. If you want to reference just soa_record, you can create the unique index on soa_record. If you want to do the entire tree, the fk stuff doesn't support that (issues with inheriting the triggers mostly). ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] creating "job numbers"
postgresql wrote:
> How are you professionals handling this problem? I like the ability to
> insert and have the system give me the number. As I grow into more
> workstations inputting the jobs I won't have to worry about chasing
> the next highest number.
Two possible ways:
1. If you can live with gaps in the job numbers, you can use
the serial data type. That is, you create your table like
CREATE TABLE jobs (
job_id serial PRIMARY KEY,
employee_id integer REFERENCES staff,
...
);
Now your application can INSERT a row not specifying an
explicit value for the job_id like
INSERT INTO jobs (employee_id, ...)
VALUES (4711, ...);
and reading the PostgreSQL assigned job_id back with
SELECT currval('jobs_job_id_seq');
Even if there are other processes doing the same
concurrently, the assigned job_id is guaranteed to be
unique and the currval() given back by your database
connection isn't affected by it.
2. If you cannot live with gaps in the numbers, have a
separate table containing counters like
CREATE TABLE app_counter (
count_nametext PRIMARY KEY,
count_val integer;
);
INSERT INTO app_counter VALUES ('job_id', 0);
Now it's the duty of your application to use transactions
and do mainly the following:
BEGIN TRANSACTION;
UPDATE app_counter set count_val = count_val + 1
WHERE count_name = 'job_id';
INSERT INTO jobs
SELECT count_val, 4711, ...
FROM app_counter WHERE count_name = 'job_id';
SELECT count_val FROM app_counter
WHERE count_name = 'job_id';
COMMIT TRANSACTION;
For method 1, transaction aborts can cause missing job_id's
because sequence numbers aren't rolled back. OTOH method 2
will lock the table app_counter at the UPDATE and release the
lock at COMMIT. So it'll have a little less throughput than
method 1, but if you really get a performance problem with
creating job's in the database, your company must be gushing
cash and there should be plenty of money for some bigger
boxes :-).
Jan
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] drop table in PL/pgSQL
When I use "drop Table ..." in PL/pgSQL, it always causes an error as "ERROR copyObject: don't know how to copy 614" JACK ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] trigger output to a file
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 go about it? My idea is to create a trigger for every insert/update that will select the new/updated record and output it to a file. Or better yet, I would like to output the actual sql query to file. Is it possible to output the result of an sql query from a trigger to a file? How? I would appreciate any answer. Thank you very much. Sherwin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
