Horton

Thread

  1. Notiffy problem

    adasko98 <adasko.86@gmail.com> — 2012-06-29T08:01:44Z

    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.

  2. Re: Notiffy problem

    Richard Huxton <dev@archonet.com> — 2012-06-29T08:29:30Z

    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

  3. Re: Notiffy problem

    adasko98 <adasko.86@gmail.com> — 2012-06-29T08:36:27Z

    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.

  4. Re: Notiffy problem

    Merlin Moncure <mmoncure@gmail.com> — 2012-06-29T13:50:31Z

    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

  5. Re: Notiffy problem

    adasko98 <adasko.86@gmail.com> — 2012-06-29T13:58:00Z

    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.

  6. Re: Notiffy problem

    Merlin Moncure <mmoncure@gmail.com> — 2012-06-29T14:53:33Z

    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

  7. Re: Notiffy problem

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-06-29T15:10:15Z

    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

  8. Re: Notiffy problem

    adasko98 <adasko.86@gmail.com> — 2012-07-03T09:08:15Z

    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.