headers
adasko98 | 29 Jun 2012 10:01
Picon

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$
(Continue reading)

Permalink | Reply |
headers
Richard Huxton | 29 Jun 2012 10:29
Favicon

Re: Notiffy problem

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

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Permalink | Reply |
headers
adasko98 | 29 Jun 2012 10:36
Picon

Re: Notiffy problem

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.

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Permalink | Reply |
headers
Merlin Moncure | 29 Jun 2012 15:50
Picon

Re: Notiffy problem

On Fri, Jun 29, 2012 at 3:29 AM, Richard Huxton <dev <at> 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

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
(Continue reading)

Permalink | Reply |
headers
adasko98 | 29 Jun 2012 15:58
Picon

Re: Notiffy problem


On Fri, Jun 29, 2012 at 3:29 AM, Richard Huxton <dev <at> 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
(Continue reading)

Permalink | Reply |
headers
Merlin Moncure | 29 Jun 2012 16:53
Picon

Re: Notiffy problem

On Fri, Jun 29, 2012 at 8:58 AM, adasko98 <adasko.86 <at> gmail.com> wrote:
>
> On Fri, Jun 29, 2012 at 3:29 AM, Richard Huxton <dev <at> 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
(Continue reading)

Permalink | Reply |
headers
Tom Lane | 29 Jun 2012 17:10
Picon

Re: Notiffy problem

Merlin Moncure <mmoncure <at> gmail.com> writes:
> On Fri, Jun 29, 2012 at 8:58 AM, adasko98 <adasko.86 <at> 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

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Permalink | Reply |
headers
adasko98 | 3 Jul 2012 11:08
Picon

Re: Notiffy problem

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.

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Permalink | Reply |
headers
Kevin Grittner | 3 Jul 2012 14:44
Favicon

Re: Notiffy problem

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

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Permalink | Reply |

Gmane