pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedfailed to execute the psql case statement which has the function call.
7+ messages / 5 participants
[nested] [flat]
* failed to execute the psql case statement which has the function call.
@ 2022-04-05 08:00 Nikhil Ingale <niks.bgm@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Nikhil Ingale @ 2022-04-05 08:00 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi All,
I've a case statement where I'm calling the user defined postgres function
and failed to execute the same. Because it's assuming the variable passed
inside the function as a column for the table.
ERROR: column "attributes" does not exist
LINE 1: ... WHEN status_code = 0 THEN CASE WHEN bmask(attributes...
Need help to resolve this problem.
Regards,
Nikhil Ingale
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: failed to execute the psql case statement which has the function call.
@ 2022-04-05 09:07 hubert depesz lubaczewski <depesz@depesz.com>
parent: Nikhil Ingale <niks.bgm@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: hubert depesz lubaczewski @ 2022-04-05 09:07 UTC (permalink / raw)
To: Nikhil Ingale <niks.bgm@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Tue, Apr 05, 2022 at 01:30:14PM +0530, Nikhil Ingale wrote:
> Hi All,
>
> I've a case statement where I'm calling the user defined postgres function
> and failed to execute the same. Because it's assuming the variable passed
> inside the function as a column for the table.
>
> ERROR: column "attributes" does not exist
> LINE 1: ... WHEN status_code = 0 THEN CASE WHEN bmask(attributes...
>
> Need help to resolve this problem.
Well, we need to see the function. Otherwise all *I* can tell is: well,
you have to find source of the problem, and fix it.
Best regards,
depesz
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: failed to execute the psql case statement which has the function call.
@ 2022-04-05 13:34 David G. Johnston <david.g.johnston@gmail.com>
parent: Nikhil Ingale <niks.bgm@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2022-04-05 13:34 UTC (permalink / raw)
To: Nikhil Ingale <niks.bgm@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Tuesday, April 5, 2022, Nikhil Ingale <niks.bgm@gmail.com> wrote:
> Hi All,
>
> I've a case statement where I'm calling the user defined postgres function
> and failed to execute the same. Because it's assuming the variable passed
> inside the function as a column for the table.
>
You cannot use variables for identifiers in raw sql. If you need to do
that you must use dynamic sql - build a string using format and execute it.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: failed to execute the psql case statement which has the function call.
@ 2022-04-05 13:46 sagar jadhav <sagarjdhv5@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: sagar jadhav @ 2022-04-05 13:46 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Nikhil Ingale <niks.bgm@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Use single quotes ('attribute') for variable in function.
On Tue, Apr 5, 2022 at 7:04 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:
>
>
> On Tuesday, April 5, 2022, Nikhil Ingale <niks.bgm@gmail.com> wrote:
>
>> Hi All,
>>
>> I've a case statement where I'm calling the user defined postgres
>> function and failed to execute the same. Because it's assuming the variable
>> passed inside the function as a column for the table.
>>
>
> You cannot use variables for identifiers in raw sql. If you need to do
> that you must use dynamic sql - build a string using format and execute it.
>
> David J.
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: failed to execute the psql case statement which has the function call.
@ 2022-04-05 13:53 David G. Johnston <david.g.johnston@gmail.com>
parent: sagar jadhav <sagarjdhv5@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2022-04-05 13:53 UTC (permalink / raw)
To: sagar jadhav <sagarjdhv5@gmail.com>; +Cc: Nikhil Ingale <niks.bgm@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Tue, Apr 5, 2022 at 6:47 AM sagar jadhav <sagarjdhv5@gmail.com> wrote:
> Use single quotes ('attribute') for variable in function.
>
That just produces the literal variable - it doesn't cause a substitution
of the variable's value into the query. And if the position of usage does
need a column identifier it still will produce a syntax error.
But the error fragment shown suggests that the query shouldn't be expecting
a column name (contrary to my earlier point), so we are back to having to
see the entire function definition (or a reproducible example) to provide
an answer. Spelling typo comes to mind.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: failed to execute the psql case statement which has the function call.
@ 2022-04-05 14:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2022-04-05 14:10 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: sagar jadhav <sagarjdhv5@gmail.com>; Nikhil Ingale <niks.bgm@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> But the error fragment shown suggests that the query shouldn't be expecting
> a column name (contrary to my earlier point), so we are back to having to
> see the entire function definition (or a reproducible example) to provide
> an answer. Spelling typo comes to mind.
I'm wondering if there's an actual function involved at all.
Maybe the OP is just writing this in a psql script and wanting
to substitute a psql variable. In that case something like
:'variable' might be the solution.
Bottom line is the same though: we need to see a lot more context
than we've been shown.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: failed to execute the psql case statement which has the function call.
@ 2022-04-05 15:13 Nikhil Ingale <niks.bgm@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Nikhil Ingale @ 2022-04-05 15:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; sagar jadhav <sagarjdhv5@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Thanks folks, issue got resolved.
On Tue, Apr 5, 2022 at 7:40 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > But the error fragment shown suggests that the query shouldn't be
> expecting
> > a column name (contrary to my earlier point), so we are back to having to
> > see the entire function definition (or a reproducible example) to provide
> > an answer. Spelling typo comes to mind.
>
> I'm wondering if there's an actual function involved at all.
> Maybe the OP is just writing this in a psql script and wanting
> to substitute a psql variable. In that case something like
> :'variable' might be the solution.
>
> Bottom line is the same though: we need to see a lot more context
> than we've been shown.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2022-04-05 15:13 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-04-05 08:00 failed to execute the psql case statement which has the function call. Nikhil Ingale <niks.bgm@gmail.com>
2022-04-05 09:07 ` hubert depesz lubaczewski <depesz@depesz.com>
2022-04-05 13:34 ` David G. Johnston <david.g.johnston@gmail.com>
2022-04-05 13:46 ` sagar jadhav <sagarjdhv5@gmail.com>
2022-04-05 13:53 ` David G. Johnston <david.g.johnston@gmail.com>
2022-04-05 14:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-04-05 15:13 ` Nikhil Ingale <niks.bgm@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox