Re: [SQL] handling error in a function
> > i made desperate efforts with handling errors in a function. > > I am using functions for encapsulating a few sql-statements. Please have a > look at this: > > CREATE FUNCTION sp_fdelce(int4) RETURNS int4 AS ' > DECLARE > id ALIAS FOR $1; > BEGIN >DELETE FROM f_ces WHERE fce_id = id; > >-- "virtual code follows" >IF ERROR > RETURN 0; >ELSE > RETURN 1; > END; > ' > LANGUAGE 'plpgsql'; > > Not difficult. I know. But fce_id is used as a foreign key by other tables. > When executing this and violating that constraint (i mustn't delete that > row), the function aborts with "unknown error" and i have no way to return 0 or > something like that. > > I am programming with PHP and PEAR, each time, the result set is an object > of type error, the script jumps to an error page, to calm the angry customers. > > > Especially in this case I don't want to jump to the error page, i want to > tell the user with a normal Messageline: Sorry, you mustn't delete that > element. > > I can't handle this error? Is that right? I really have no way to catch that > foreign key violence? > Sure you can, but not directly. Before deleting you should check for the error condition, possibly by querying system tables especially pg_relcheck. So, if the error condition matches, don't delete but generate your message line. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] handling error in a function
Peter Gabriel wrote: When executing this and violating that constraint (i mustn't delete that row), the function aborts with "unknown error" and i have no way to return 0 or something like that. I am programming with PHP and PEAR, each time, the result set is an object of type error, the script jumps to an error page, to calm the angry customers. I don't know, how PHP works with Postgresql, but in psql I get such error, when deleting wrong row: ERROR: referential integrity violation - key in table_master still referenced from table_detail Maybe it is possible some way to access this error inside PHP? Tomasz Myrta ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Dynamic sql program using libpq
>
> I am able to find couple
> of examples for dynamic sql programming through ecpg. But i want to
> do dynamic sql programming through libpq.
> If anyone has programs doing the dynamic sql programmming using the
> libpq libraries please mail to me.
>
Attached you'll find my encapsulated library of postgres functions,
which I use for all queries resp. commands.
Regards, Christoph
/*-
*
* libpq-myfe.h
*
*
* Christoph Haller, D.T.I.
*
* Created Sep 2001
*
*-
*/
#include "libpq-fe.h"
#ifndef LIBPQ_MYFE_H
#define LIBPQ_MYFE_H
int PGSQL_command(PGconn *thisconnec, PGresult *thisresul,
char *thiscommand);
int PGSQL_process(PGconn *thisconnec, PGresult **thisresul,
char *thiscommand);
int PGSQL_begin(PGconn *thisconnec);
int PGSQL_declare_cursor1(PGconn *thisconnec, PGresult *thisresul,
char *thisselect);
int PGSQL_fetch_all1(PGconn *thisconnec, PGresult **thisresul);
int PGSQL_close_cursor1(PGconn *thisconnec, PGresult *thisresul);
int PGSQL_commit(PGconn *thisconnec);
int PGSQL_rollback(PGconn *thisconnec);
int PGSQL_declare_cursor2(PGconn *thisconnec, PGresult *thisresul,
char *thisselect);
int PGSQL_fetch_all2(PGconn *thisconnec, PGresult **thisresul);
int PGSQL_close_cursor2(PGconn *thisconnec, PGresult *thisresul);
int PGSQL_declare_bincsr1(PGconn *thisconnec, PGresult *thisresul,
char *thisselect);
int PGSQL_fetch_binall1(PGconn *thisconnec, PGresult **thisresul);
int PGSQL_close_bincsr1(PGconn *thisconnec, PGresult *thisresul);
int PGSQL_binprocess(PGconn *thisconnec, PGresult **thisresul,
char *thiscommand);
#endif /* LIBPQ_MYFE_H */
#include
#include
#include
#include "libpq-myfe.h"
char *strdup(const char *s);
#ifdef PostgreSQL_6_5_3
const char *PGSQL_UNIQUE_INDEX_VIOLATED = /* PostgreSQL 6.5.3 */
"ERROR: Cannot insert a duplicate key into a unique index" ;
#endif /* PostgreSQL_6_5_3 */
const char *PGSQL_UNIQUE_INDEX_VIOLATED = /* PostgreSQL 7.1.2 */
"ERROR: Cannot insert a duplicate key into unique index " ;
/*
** procedure: trim_sequence
** purpose: delete space sequences within a given string
** delete spaces only within the given length
** the given string does not have to be null terminated
** this function is secure:
** - one space always remains
** - no space, no action
** - several space sequences are processed
** - deleting stops if a null character is found
**and the given length is not reached
**
** parameters: seq - the string to trim
** seq_len - the length of string to trim
** returns: the trimmed string
*/
# if defined(__STDC__) || defined(__cplusplus)
char *trim_sequence(char *seq,size_t seq_len)
# else /* __STDC__ || __cplusplus */
char *trim_sequence(seq,seq_len)
char *seq;size_t seq_len;
# endif /* __STDC__ || __cplusplus */
{
char space=' '; /* the trim character */
char *first=seq; /* ptr to the 1st space */
char *last; /* ptr to the last space */
size_t seq_pos=0;/* index within sequence */
/* while not end of sequence ... */
while(seq_pos continue search */
while(last&&*last==space&&last-seqfirst) {
*first=NULL; /* set temporary termination */
strcat(seq,last); /* append the following subsequence */
}
seq_pos=last-seq; /* update sequence index */
}
return seq; /* return the trimmed sequence */
} /* trim_sequence() */
int PGSQL_command(PGconn *thisconnec, PGresult *thisresul,
char *thiscommand)
{
int result = EXIT_SUCCESS;
trim_sequence(thiscommand, strlen(thiscommand));
thisresul = PQexec(thisconnec, thiscommand);
if (!thisresul || PQresultStatus(thisresul) != PGRES_COMMAND_OK)
{
fprintf(stderr, "'%s' command failed\n%s\n", thiscommand,
PQresultErrorMessage(thisresul));
result = -EXIT_FAILURE;
}
else
{
result = atoi(PQcmdTuples(thisresul));
}
/* PQclear PGresult whenever it is no longer needed to avoid memory leaks */
PQclear(thisresul);
return result;
}
int PGSQL_process(PGconn *thisconnec, PGresult **thisresul,
char *thiscommand)
{
int result = EXIT_SUCCESS;
trim_sequence(thiscommand, strlen(thiscommand));
*thisresul = PQexec(thisconnec, thiscommand);
if (!*thisresul || PQresultStatus(*thisresul) != PGRES_TUPLES_OK)
{
fprintf(stderr, "'%s' command failed\n%s\n", thiscommand,
PQresultErrorMessage(*thisresul));
result = -EXIT_FAILURE;
}
else
{
result = PQntuples(*thisresul);
}
/* PQ
Re: [SQL] pl/pgsql question
Ludwig Lim wrote: Try changing the "AFTER" to "BEFORE" CREATE TRIGGER projtask_insert_depend_trig BEFORE... Changes made to the "NEW" will not be reflect in the AFTER trigger since, the row is already inserted. Thanks, however this seems to present a different problem now. FOR dependon IN SELECT * FROM project_depend_vw WHERE project_task_id=NEW.project_task_id LOOP That loop apparently does not find any matching rows, which would have been inserted just before this row was, inside the same transaction. It was successfully finding those rows before, when the trigger was AFTER INSERT. If I manually select those rows after the query is committed, I am able to pull up the matching rows. Tim ---(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] Error code for database down
HI... When i executed a query using PQexec..It returned error. Is there any way to check this error is because of Database is down. like PGRES_EMPTY_QUERY -- The string sent to the backend was empty. PGRES_COMMAND_OK -- Successful completion of a command returning no data PGRES_TUPLES_OK -- The query successfully executed etc.. Like this is there any value which will specify the database failure? Thanks in advance -Naren. ---(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] pl/pgsql question
Tim, > That loop apparently does not find any matching rows, which would > have been inserted just before this row was, inside the same > transaction. > > It was successfully finding those rows before, when the trigger was > AFTER INSERT. If I manually select those rows after the query is > committed, I am able to pull up the matching rows. I think that triggers are probably not a good strategy for the kind of calculation you're doing. I'd suggest instead a middleware module or a "data push" function which would bundle all of the calculation logic before calling any of the inserts. -Josh ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] references table(multiple columns go here)
Hi folks,
how do I define a referene from 2 columns in 1 table to 2 columns in another.
I have:
create table ranks (
rid int4 default nextval('ranks_rid_seq'::text) unique not null,
rdidcharacter references depts(did), -- department
rrank int4 not null, -- departmental rank
rdesc character varying(40) -- Rank Description
);
create unique index "ranks_drank_index" on ranks using btree ("rdid",
"rrank");
copy "ranks" from stdin;
1 O 1 Trainee TTI
2 O 2 TTI
3 M 1 Cleaner
4 M 2 Passed Cleaner
5 M 3 Fireman.
\.
I would now like to define the following table so that inserts can only happen
if jdid matches rdid and jrank matches rrank.
create table jobtypes (
jid int4 default nextval('jobs_jid_seq'::text) unique not null,
jdidcharacter references ranks(rdid), -- This joint reference
jrank int4 not null references ranks(rrank), -- needs sorting
jdesc character varying(40) -- job description
);
copy "jobtypes" from stdin;
1 M 3 Charge Cleaner
2 O 3 Lock Carriages
\.
(I want the first row to work and the second to be rejected)
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] references table(multiple columns go here)
Hello again
Gary Stainburn wrote:
Hi folks,
how do I define a referene from 2 columns in 1 table to 2 columns in
another.
I have:
create table ranks (
rid int4 default nextval('ranks_rid_seq'::text) unique not null,
rdid character references depts(did), -- department
rrank int4 not null, -- departmental rank
rdesc character varying(40) -- Rank Description
);
I would now like to define the following table so that inserts can
only happen
if jdid matches rdid and jrank matches rrank.
create table jobtypes (
jid int4 default nextval('jobs_jid_seq'::text) unique not null,
jdid character references ranks(rdid), -- This joint reference
jrank int4 not null references ranks(rrank), -- needs sorting
jdesc character varying(40) -- job description
!!!here!!! ,CONSTRAINT c2 FOREIGN KEY(jid,jdid) REFERENCES ranks (rid,rdid)
);
that's all
Tomasz Myrta
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] pl/pgsql question
Josh Berkus wrote: Tim, That loop apparently does not find any matching rows, which would have been inserted just before this row was, inside the same transaction. It was successfully finding those rows before, when the trigger was AFTER INSERT. If I manually select those rows after the query is committed, I am able to pull up the matching rows. I think that triggers are probably not a good strategy for the kind of calculation you're doing. I'd suggest instead a middleware module or a "data push" function which would bundle all of the calculation logic before calling any of the inserts. Yeah, but this is so much cooler. ;-) Essentially this would be like recursion to push back/pull forward tasks which are dependent on each other. The "UPDATE" trigger I wrote is about 5x longer. I guess I can push this back into the PHP code and do a recusive function call, but that seems less sexy. Tim ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] references table(multiple columns go here)
Hi Tomasz,
On Wednesday 18 December 2002 4:46 pm, Tomasz Myrta wrote:
> Hello again
>
> Gary Stainburn wrote:
> > Hi folks,
> >
> > how do I define a referene from 2 columns in 1 table to 2 columns in
> > another.
> >
> > I have:
> >
> > create table ranks (
> > rid int4 default nextval('ranks_rid_seq'::text) unique not null,
> > rdidcharacter references depts(did), -- department
> > rrank int4 not null, -- departmental rank
> > rdesc character varying(40) -- Rank Description
> > );
> >
> >
> >
> >
> > I would now like to define the following table so that inserts can
> > only happen
> > if jdid matches rdid and jrank matches rrank.
> >
> > create table jobtypes (
> > jid int4 default nextval('jobs_jid_seq'::text) unique not null,
> > jdidcharacter references ranks(rdid), -- This joint reference
> > jrank int4 not null references ranks(rrank), -- needs sorting
> > jdesc character varying(40) -- job description
>
> !!!here!!! ,CONSTRAINT c2 FOREIGN KEY(jid,jdid) REFERENCES ranks (rid,rdid)
Did this come in with 7.2? I get parse error on or near 'FOREIGN'. Note I
changed the field names to the ones I wanted.
create table jobtypes (
jid int4 default nextval('jobs_jid_seq'::text) unique not null,
jdidcharacter, -- This joint reference
jrank int4 not null references ranks(rrank), -- needs sorting
jdesc character varying(40), -- job description
contraint c2 foreign key (jdid,jrank) references ranks (rdid,rrank)
);
ERROR: parser: parse error at or near "foreign"
>
> > );
>
> that's all
> Tomasz Myrta
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] unsubscribe
unsubscribe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] references table(multiple columns go here)
On Wednesday 18 December 2002 4:56 pm, Gary Stainburn wrote:
> Hi Tomasz,
[snip]
> > > create table jobtypes (
> > > jid int4 default nextval('jobs_jid_seq'::text) unique not null,
> > > jdid character references ranks(rdid), -- This joint reference
> > > jrank int4 not null references ranks(rrank), -- needs sorting
> > > jdesc character varying(40) -- job description
> >
> > !!!here!!! ,CONSTRAINT c2 FOREIGN KEY(jid,jdid) REFERENCES ranks
> > (rid,rdid)
>
> Did this come in with 7.2? I get parse error on or near 'FOREIGN'. Note I
> changed the field names to the ones I wanted.
I've just tried this on a 7.2.1-5 system and get the same error.
>
> create table jobtypes (
> jid int4 default nextval('jobs_jid_seq'::text) unique not null,
> jdid character, -- This joint reference
> jrank int4 not null references ranks(rrank), -- needs sorting
> jdesc character varying(40), -- job description
> contraint c2 foreign key (jdid,jrank) references ranks (rdid,rrank)
> );
> ERROR: parser: parse error at or near "foreign"
>
> > > );
> >
> > that's all
> > Tomasz Myrta
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(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] references table(multiple columns go here)
Gary Stainburn <[EMAIL PROTECTED]> writes:
> I've just tried this on a 7.2.1-5 system and get the same error.
>> create table jobtypes (
>> jid int4 default nextval('jobs_jid_seq'::text) unique not null,
>> jdid character, -- This joint reference
>> jrankint4 not null references ranks(rrank), -- needs sorting
>> jdesccharacter varying(40), -- job description
>> contraint c2 foreign key (jdid,jrank) references ranks (rdid,rrank)
^
>> );
>> ERROR: parser: parse error at or near "foreign"
If that's an accurate transcription, I think "contraint" -> "constraint"
would help...
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] references table(multiple columns go here)
Tom Lane wrote:
Gary Stainburn writes:
>I've just tried this on a 7.2.1-5 system and get the same error.
>>create table jobtypes (
>>jid int4 default nextval('jobs_jid_seq'::text) unique not null,
>>jdid character, -- This joint reference
>>jrank int4 not null references ranks(rrank), -- needs sorting
>>jdesc character varying(40), -- job description
>>contraint c2 foreign key (jdid,jrank) references ranks (rdid,rrank)
^
>>);
>>ERROR: parser: parse error at or near "foreign"
If that's an accurate transcription, I think "contraint" -> "constraint"
would help...
That's right. The letter has gone somewhere...
Tomasz Myrta
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
