Обсуждение: Notiffy problem
Hi
In first sorry for my english :) I have got a problem with notify/listener.
I do a function which returns a trigger. Everything is ok but when i want
send in a second parameter a variable NOTIFY say: "syntax error"
This is works example with no variable:
CREATE OR REPLACE FUNCTION notify_demo()
RETURNS trigger AS
$BODY$
DECLARE
BEGIN
Notify demoApp, 'some text';
RETURN null;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
This is example with variable (not work):
CREATE OR REPLACE FUNCTION notify_demo()
RETURNS trigger AS
$BODY$
DECLARE
n_user text;
BEGIN
n_user :='sda';
Notify demoApp, n_user ; <----here is a problem
RETURN null;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Notiffy-problem-tp5714744.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 29/06/12 09:01, adasko98 wrote: > Hi > In first sorry for my english :) I have got a problem with notify/listener. > I do a function which returns a trigger. Everything is ok but when i want > send in a second parameter a variable NOTIFY say: "syntax error" > Notify demoApp, 'some text'; > n_user :='sda'; > Notify demoApp, n_user ;<----here is a problem Looks like a limitation of the plpgsql parser, perhaps even counts as a bug. You can work around it with EXECUTE though, something like: cmd := 'NOTIFY demoApp, ' || quote_literal(n_user); EXECUTE cmd; or just EXECUTE 'NOTIFY demoApp, ' || quote_literal(n_user); -- Richard Huxton Archonet Ltd
Thanks for your answer. Now it works. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Notiffy-problem-tp5714744p5714750.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Fri, Jun 29, 2012 at 3:29 AM, Richard Huxton <dev@archonet.com> wrote: > On 29/06/12 09:01, adasko98 wrote: >> >> Hi >> In first sorry for my english :) I have got a problem with >> notify/listener. >> I do a function which returns a trigger. Everything is ok but when i want >> send in a second parameter a variable NOTIFY say: "syntax error" > > >> Notify demoApp, 'some text'; > > >> n_user :='sda'; >> Notify demoApp, n_user ;<----here is a problem > > > Looks like a limitation of the plpgsql parser, perhaps even counts as a bug. > You can work around it with EXECUTE though, something like: > cmd := 'NOTIFY demoApp, ' || quote_literal(n_user); > EXECUTE cmd; > or just > EXECUTE 'NOTIFY demoApp, ' || quote_literal(n_user); also see pg_notify() function. merlin
On Fri, Jun 29, 2012 at 3:29 AM, Richard Huxton <dev@archonet.com> wrote: > On 29/06/12 09:01, adasko98 wrote: >> >> Hi >> In first sorry for my english :) I have got a problem with >> notify/listener. >> I do a function which returns a trigger. Everything is ok but when i want >> send in a second parameter a variable NOTIFY say: "syntax error" > > >> Notify demoApp, 'some text'; > > >> n_user :='sda'; >> Notify demoApp, n_user ;<----here is a problem > > > Looks like a limitation of the plpgsql parser, perhaps even counts as a > bug. > You can work around it with EXECUTE though, something like: > cmd := 'NOTIFY demoApp, ' || quote_literal(n_user); > EXECUTE cmd; > or just > EXECUTE 'NOTIFY demoApp, ' || quote_literal(n_user); also see pg_notify() function. Yes i'm looking for that. But i connect c# application with postgres and pg_notify() don't work with my notify event. Anyway thanks for help -- View this message in context: http://postgresql.1045698.n5.nabble.com/Notiffy-problem-tp5714744p5714782.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Fri, Jun 29, 2012 at 8:58 AM, adasko98 <adasko.86@gmail.com> wrote:
>
> On Fri, Jun 29, 2012 at 3:29 AM, Richard Huxton <dev@archonet.com> wrote:
>> On 29/06/12 09:01, adasko98 wrote:
>>>
>>> Hi
>>> In first sorry for my english :) I have got a problem with
>>> notify/listener.
>>> I do a function which returns a trigger. Everything is ok but when i want
>>> send in a second parameter a variable NOTIFY say: "syntax error"
>>
>>
>>> Notify demoApp, 'some text';
>>
>>
>>> n_user :='sda';
>>> Notify demoApp, n_user ;<----here is a problem
>>
>>
>> Looks like a limitation of the plpgsql parser, perhaps even counts as a
>> bug.
>> You can work around it with EXECUTE though, something like:
>> cmd := 'NOTIFY demoApp, ' || quote_literal(n_user);
>> EXECUTE cmd;
>> or just
>> EXECUTE 'NOTIFY demoApp, ' || quote_literal(n_user);
>
> also see pg_notify() function.
>
> Yes i'm looking for that. But i connect c# application with postgres and
> pg_notify() don't work with my notify event. Anyway thanks for help
huh? pg_notify() is just an alternate way of sending notifications.
it should be available from any client stack that allows calling
custom backend functions(including C#).
postgres=# listen test;
LISTEN
postgres=# select pg_notify('test', 'hello!');
pg_notify
-----------
(1 row)
Asynchronous notification "test" with payload "hello!" received from
server process with PID 31740.
merlin
Merlin Moncure <mmoncure@gmail.com> writes:
> On Fri, Jun 29, 2012 at 8:58 AM, adasko98 <adasko.86@gmail.com> wrote:
>>>> Notify demoApp, n_user ;<----here is a problem
>>> Looks like a limitation of the plpgsql parser, perhaps even counts as a
>>> bug.
It is not a bug, but a documented limitation of the NOTIFY command: the
payload has to be a simple string literal.
(The technical reason for that is that NOTIFY isn't a plannable
statement, but a utility command, and utility commands generally don't
evaluate expressions. In principle we could fix that, but in practice
it's not going to change, because the pg_notify() function serves just
fine for every case where you want a non-constant payload.)
regards, tom lane
Hi thanks for help. Now i know why pg_notify() does not works for me. I'm
named listener in c# code 'Demo' and this is a problem. In name can't be a
capital letter because postges change this name to small letter i think. So
if someone want use pg_notify use only small letter like this
"pg_notify('demo', variable);" and it will be works
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Notiffy-problem-tp5714744p5715157.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
adasko98 wrote:
> Hi thanks for help. Now i know why pg_notify() does not works for
> me. I'm named listener in c# code 'Demo' and this is a problem. In
> name can't be a capital letter because postges change this name to
> small letter i think. So if someone want use pg_notify use only
> small letter like this "pg_notify('demo', variable);" and it will
> be works
The channel for a LISTEN or NOTIFY command is an identifier, so it
follows the normal rules for identifiers, including folding to lower
case if not enclosed in quotes. The pg_notify() function wraps the
given channel name in quotes if needed. Most people find it easiest
to keep all identifiers lower case to avoid such issues.
-Kevin