agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedFunction Problem
9+ messages / 8 participants
[nested] [flat]
* Function Problem
@ 2002-12-11 12:38 Geoff <geoff@metalogicplc.com>
2002-12-11 12:56 ` Re: Function Problem Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
2002-12-11 15:56 ` Re: Function Problem Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 2 replies; 9+ messages in thread
From: Geoff @ 2002-12-11 12:38 UTC (permalink / raw)
To: pgsql-admin
I've got this function which works off a trigger.
The trigger is calling the function ok, but I get this error.
<error>
NOTICE: plpgsql: ERROR during compile of mon_sum_update near line 43
ERROR: parse error at or near ""
Now line 43 is this either the return null or end statement at the end of
the function...
RETURN NULL;
END
Can anyone see what I've done wrong in there?
TIA
Geoff
Here is my trigger and function.
trigger
======
CREATE TRIGGER doc_status_trig AFTER UPDATE ON document_status FOR EACH ROW
EXECUTE PROCEDURE mon_sum_update()
function
======
CREATE FUNCTION mon_sum_upd () RETURN OPAQUE AS '
BEGIN
-- Ensure we have a record that is valid .
IF ( ! NEW.direction && NEW.direction && NEW.msgtype && NEW.status )
THEN
RETURN NULL;
-- Ensure the record exists in the monitor_summary table.
IF ( ! EXISTS SELECT * FROM monitor_summary WHERE unit = NEW.unit and
msgtype = NEW.msgtype and direction = NEW.direction and status =
NEW.status )
THEN
INSERT INTO monitor_summary ( version, cdate, mdate, direction, unit,
msgtype, status ) VALUES ( 1, 'now', 'now', 'NEW.direction', 'NEW.unit',
'NEW.msgtype', 'NEW.status' );
-- Ensure OLD and NEW status's are different.
IF ( NEW.status == OLD.status )
THEN
RETURN NULL;
-- Update the OLD status record. ( -1 )
UPDATE monitor_summary
SET
total = total - 1
WHERE
direction = OLD.direction AND unit = OLD.unit AND msgtype = OLD.msgtype AND
status = OLD.status ;
-- Update the NEW status record. ( +1 )
UPDATE monitor_summary
SET
total = total + 1
WHERE
direction = OLD.direction AND unit = OLD.unit AND msgtype = OLD.msgtype AND
status = NEW.status ;
RETURN NULL;
END
' LANGUAGE 'plpgsql' ;
I'm using pgAdminII to insert this function, and it goes in ok..
- Geoff Ellis
- +44(0)2476678484
.-----------------------------------------------------------------.
/ .-. This message is intended only for the person or .-. \
| / \ entity to which it is addressed and may contain / \ |
| |\_. | confidential and/or privileged material. Any | ._/| |
|\| | /| review, retransmission, dissemination or other |\ | |/|
| `---' | use of, or taking of any action in reliance upon, | `---' |
| | this information by persons or entities other than | |
| | the intended recipient is prohibited. If you get | |
| | this message in error please contact the sender | |
| | by return e-mail and delete the message from your | |
| | computer. Any opinions contained in this message | |
| | are those of the author and are not given or | |
| | endorsed by Metalogic PLC unless otherwise clearly | |
| | indicated in this message and the authority of the | |
| | author to bind Metalogic is duly verified. | |
| | | |
| | Metalogic PLC accepts no liability for any errors | |
| | or omissions in the context of this message which | |
| | arise as a result of internet transmission. | |
| |-----------------------------------------------------| |
\ | | /
\ / \ /
`---' `---'
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Function Problem
2002-12-11 12:38 Function Problem Geoff <geoff@metalogicplc.com>
@ 2002-12-11 12:56 ` Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
1 sibling, 0 replies; 9+ messages in thread
From: Jakub Ouhrabka @ 2002-12-11 12:56 UTC (permalink / raw)
To: Geoff <geoff@metalogicplc.com>; +Cc: pgsql-admin
hi,
> I've got this function which works off a trigger.
> The trigger is calling the function ok, but I get this error.
> <error>
> NOTICE: plpgsql: ERROR during compile of mon_sum_update near line 43
> ERROR: parse error at or near ""
>
> Now line 43 is this either the return null or end statement at the end of
> the function...
>
> RETURN NULL;
>
> END
unfortunately, the error messages aren't always accurate in plpgsql...
> Can anyone see what I've done wrong in there?
i think there are few mistakes: there is missing "END IF;" in all IF
statements, it should look like this:
IF (condition) THEN
statement;
END IF;
also conditions aren't always perfect, you should use "=" instead of
"==" for comparison in plpgsql.
don't use aposthrophes inside the function, it isn't necessary for
expressions like new.field... if you really want them, use double
aposthrophes "''" - you must quote them inside the plpgsql function.
and may be there are some other mistakes. i would suggest to study syntax
of plpgsql at http://www.postgresql.org/idocs/index.php?plpgsql.html
carefully. it can save you a lot of time when debugging functions - the
compiler isn't very wise when reporting errors...
hth,
kuba
>
> TIA
>
> Geoff
>
>
>
> Here is my trigger and function.
>
> trigger
> ======
> CREATE TRIGGER doc_status_trig AFTER UPDATE ON document_status FOR EACH ROW
> EXECUTE PROCEDURE mon_sum_update()
>
> function
> ======
> CREATE FUNCTION mon_sum_upd () RETURN OPAQUE AS '
>
> BEGIN
>
> -- Ensure we have a record that is valid .
>
> IF ( ! NEW.direction && NEW.direction && NEW.msgtype && NEW.status )
> THEN
> RETURN NULL;
>
>
> -- Ensure the record exists in the monitor_summary table.
>
> IF ( ! EXISTS SELECT * FROM monitor_summary WHERE unit = NEW.unit and
> msgtype = NEW.msgtype and direction = NEW.direction and status =
> NEW.status )
> THEN
> INSERT INTO monitor_summary ( version, cdate, mdate, direction, unit,
> msgtype, status ) VALUES ( 1, 'now', 'now', 'NEW.direction', 'NEW.unit',
> 'NEW.msgtype', 'NEW.status' );
>
> -- Ensure OLD and NEW status's are different.
>
> IF ( NEW.status == OLD.status )
> THEN
> RETURN NULL;
>
>
> -- Update the OLD status record. ( -1 )
>
> UPDATE monitor_summary
> SET
> total = total - 1
> WHERE
> direction = OLD.direction AND unit = OLD.unit AND msgtype = OLD.msgtype AND
> status = OLD.status ;
>
> -- Update the NEW status record. ( +1 )
>
> UPDATE monitor_summary
> SET
> total = total + 1
> WHERE
> direction = OLD.direction AND unit = OLD.unit AND msgtype = OLD.msgtype AND
> status = NEW.status ;
>
> RETURN NULL;
>
> END
>
> ' LANGUAGE 'plpgsql' ;
>
>
> I'm using pgAdminII to insert this function, and it goes in ok..
>
> - Geoff Ellis
> - +44(0)2476678484
>
> .-----------------------------------------------------------------.
> / .-. This message is intended only for the person or .-. \
> | / \ entity to which it is addressed and may contain / \ |
> | |\_. | confidential and/or privileged material. Any | ._/| |
> |\| | /| review, retransmission, dissemination or other |\ | |/|
> | `---' | use of, or taking of any action in reliance upon, | `---' |
> | | this information by persons or entities other than | |
> | | the intended recipient is prohibited. If you get | |
> | | this message in error please contact the sender | |
> | | by return e-mail and delete the message from your | |
> | | computer. Any opinions contained in this message | |
> | | are those of the author and are not given or | |
> | | endorsed by Metalogic PLC unless otherwise clearly | |
> | | indicated in this message and the authority of the | |
> | | author to bind Metalogic is duly verified. | |
> | | | |
> | | Metalogic PLC accepts no liability for any errors | |
> | | or omissions in the context of this message which | |
> | | arise as a result of internet transmission. | |
> | |-----------------------------------------------------| |
> \ | | /
> \ / \ /
> `---' `---'
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Function Problem
2002-12-11 12:38 Function Problem Geoff <geoff@metalogicplc.com>
@ 2002-12-11 15:56 ` Robert Treat <xzilla@users.sourceforge.net>
1 sibling, 0 replies; 9+ messages in thread
From: Robert Treat @ 2002-12-11 15:56 UTC (permalink / raw)
To: geoff@metalogicplc.com; +Cc: pgsql-admin
On Wed, 2002-12-11 at 07:38, Geoff wrote:
>
> RETURN NULL;
>
> END
>
> ' LANGUAGE 'plpgsql' ;
>
Look over Jakub's email, it's on the right track. FWIW, you also need a
; after your END statement, which I believe is the actual error your
seeing.
Robert Treat
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Function Problem
@ 2002-12-11 15:55 Geoff <geoff@metalogicplc.com>
0 siblings, 0 replies; 9+ messages in thread
From: Geoff @ 2002-12-11 15:55 UTC (permalink / raw)
To: pgsql-admin
Thanks guys, I've cracked it now... yeah, a lot of syntax errors, but to be
honest, I never got to look at the pl syntax page. Thanks to Jakub for the
link, it helped solve the problem...
thanks again..
Geoff
-----Original Message-----
From: Robert Treat [mailto:xzilla@users.sourceforge.net]
Sent: 11 December 2002 15:57
To: geoff@metalogicplc.com
Cc: Pgsql-Admin (E-mail)
Subject: Re: [ADMIN] Function Problem
On Wed, 2002-12-11 at 07:38, Geoff wrote:
>
> RETURN NULL;
>
> END
>
> ' LANGUAGE 'plpgsql' ;
>
Look over Jakub's email, it's on the right track. FWIW, you also need a
; after your END statement, which I believe is the actual error your
seeing.
Robert Treat
^ permalink raw reply [nested|flat] 9+ messages in thread
* Function problem.
@ 2003-01-13 15:05 Geoff Ellis <geoff@metalogicplc.com>
2003-01-13 15:58 ` Re: Function problem. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Geoff Ellis @ 2003-01-13 15:05 UTC (permalink / raw)
To: pgsql-admin
I've got a tiny problem with how I want this function to work.
On and insert/delete from a table, I want to check to see if a field
contains a certain valule. If it does, not to insert or delete the record.
If it doesn't I want to go ahead and insert/delete the record.
Here's my function.
-- Function: users_upd_del()
CREATE FUNCTION users_upd_del() RETURNS int2 AS 'BEGIN
IF ( NEW.username = ''emsroot'' ) THEN
RAISE NOTICE ''Cannot REMOVE user [ (%) ]'', NEW.username;
RETURN NULL;
END IF;
RETURN 1;
END' LANGUAGE 'plpgsql';
However, when this run I get the error
=== ERROR: fmgr_info: function 696542: cache lookup failed =====
I've tried to change the return type to opaque and remove the RETURN 1; But
if the condition isn't true then I don't have a return.
Can someone help me here, I've looked through the 7.3rc1 Docs shipped with
pgadmin and can't quite see what I'm after...
thanks
Geoff
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Function problem.
2003-01-13 15:05 Function problem. Geoff Ellis <geoff@metalogicplc.com>
@ 2003-01-13 15:58 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2003-01-13 15:58 UTC (permalink / raw)
To: geoff@metalogicplc.com; +Cc: pgsql-admin
"Geoff Ellis" <geoff@metalogicplc.com> writes:
> === ERROR: fmgr_info: function 696542: cache lookup failed =====
This is on 7.3? My first guess would have been that you deleted and
recreated the function without recreating the trigger that references
it. But 7.3 should not let you do that.
> I've tried to change the return type to opaque and remove the RETURN 1; But
> if the condition isn't true then I don't have a return.
In an update trigger, you RETURN NEW in the normal case where you want
the update to proceed.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Function problem
@ 2024-08-13 09:56 =?iso-8859-2?Q?Domen_=A9etar?= <domen.setar@izum.si>
2024-08-13 12:20 ` Re: Function problem khan Affan <bawag773@gmail.com>
2024-08-27 04:37 ` Re: Function problem Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 9+ messages in thread
From: Domen Šetar @ 2024-08-13 09:56 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi Admins
I'm using pgbouncer in my postgresql installation. For authorization purposese I use function public.lookup (https://www.cybertec-postgresql.com/en/pgbouncer-authentication-made-easy/) which is defined for each database. Since we upgraded postgresql from V14 to V16 I have problems when migrating database from one instance to another: authentication doesn't work. I have to drop function and define it again on database to make authentication work. Any idea why ?
Best regards!
[izum]
Domen Šetar
Computer Systems Support
IZUM - Institute of Information Science | Prešernova ulica 17 | 2000 Maribor | Slovenia
T: +386 2 25 20 339 | M: +386 41 676 342 | www.izum.si<http://www.izum.si; | domen.setar@izum.si<mailto:domen.setar@izum.si>
Attachments:
[image/jpeg] image002.jpg (1.3K, ../../e752541eb2524cdb86a83c76eb4d67dd@izum.si/3-image002.jpg)
download | view image
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Function problem
2024-08-13 09:56 Function problem =?iso-8859-2?Q?Domen_=A9etar?= <domen.setar@izum.si>
@ 2024-08-13 12:20 ` khan Affan <bawag773@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: khan Affan @ 2024-08-13 12:20 UTC (permalink / raw)
To: Domen Šetar <domen.setar@izum.si>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi domen
Check the function definition on both versions. Check the PgBouncer logs
for any errors or warnings related to the function or authentication. This
might provide more insight into what goes wrong during the migration. Also,
review the PostgreSQL logs for any issues related to function execution or
permission problems. Compare user MD5 hashcode in both versions and compare
it some time it update the hash code
postgres=# SELECT 'md5' || md5('test' || 'postgres');
?column?
-------------------------------------
md5633bc3c3d823be2a52d3dff94031e2c2
On Tue, Aug 13, 2024 at 2:57 PM Domen Šetar <domen.setar@izum.si> wrote:
> Hi Admins
>
>
>
> I’m using pgbouncer in my postgresql installation. For authorization
> purposese I use function public.lookup (
> https://www.cybertec-postgresql.com/en/pgbouncer-authentication-made-easy/)
> which is defined for each database. Since we upgraded postgresql from V14
> to V16 I have problems when migrating database from one instance to
> another: authentication doesn’t work. I have to drop function and define
> it again on database to make authentication work. Any idea why ?
>
>
>
> Best regards!
>
> [image: izum]
>
> Domen Šetar
> *Computer Systems Support*
> IZUM – Institute of Information Science | Prešernova ulica 17 | 2000
> Maribor | Slovenia
> T: +386 2 25 20 339 | M: +386 41 676 342 | *www.izum.si
> <http://www.izum.si>* | domen.setar@izum.si
>
>
>
>
>
Attachments:
[image/jpeg] image002.jpg (1.3K, ../../CAF4emOm-uPxsRvS0rqewF4Q37xXc-4=6WhyQHDhs-6r7e=qEhQ@mail.gmail.com/3-image002.jpg)
download | view image
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Function problem
2024-08-13 09:56 Function problem =?iso-8859-2?Q?Domen_=A9etar?= <domen.setar@izum.si>
@ 2024-08-27 04:37 ` Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 9+ messages in thread
From: Laurenz Albe @ 2024-08-27 04:37 UTC (permalink / raw)
To: Domen Šetar <domen.setar@izum.si>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Tue, 2024-08-13 at 09:56 +0000, Domen Šetar wrote:
> I’m using pgbouncer in my postgresql installation. For authorization purposese I use function
> public.lookup (https://www.cybertec-postgresql.com/en/pgbouncer-authentication-made-easy/)
> which is defined for each database. Since we upgraded postgresql from V14 to V16 I have
> problems when migrating database from one instance to another: authentication doesn’t work.
> I have to drop function and define it again on database to make authentication work. Any idea why ?
I guess that my article is at fault. It still refers to "md5" authentication rather than
"scram-sha-256", which has been the password authenticatoin method of choice for years now.
I have updated my article.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-08-27 04:37 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-12-11 12:38 Function Problem Geoff <geoff@metalogicplc.com>
2002-12-11 12:56 ` Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
2002-12-11 15:56 ` Robert Treat <xzilla@users.sourceforge.net>
2002-12-11 15:55 Re: Function Problem Geoff <geoff@metalogicplc.com>
2003-01-13 15:05 Function problem. Geoff Ellis <geoff@metalogicplc.com>
2003-01-13 15:58 ` Re: Function problem. Tom Lane <tgl@sss.pgh.pa.us>
2024-08-13 09:56 Function problem =?iso-8859-2?Q?Domen_=A9etar?= <domen.setar@izum.si>
2024-08-13 12:20 ` Re: Function problem khan Affan <bawag773@gmail.com>
2024-08-27 04:37 ` Re: Function problem Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox