Postgres Pro
Downloads
Home   >   mailing lists

Thread: Notiffy problem

Notiffy problem

From
adasko98
Date:
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.

Re: Notiffy problem

From
Richard Huxton
Date:
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

Re: Notiffy problem

From
adasko98
Date:
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.

Re: Notiffy problem

From
Merlin Moncure
Date:
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

Re: Notiffy problem

From
adasko98
Date:
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.

Re: Notiffy problem

From
Merlin Moncure
Date:
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

Re: Notiffy problem

From
Tom Lane
Date:
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

Re: Notiffy problem

From
adasko98
Date:
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.

Re: Notiffy problem

From
"Kevin Grittner"
Date:
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