Thread: failed to execute the psql case statement which has the function call.
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...
LINE 1: ... WHEN status_code = 0 THEN CASE WHEN bmask(attributes...
Need help to resolve this problem.
Regards,
Nikhil Ingale
Re: failed to execute the psql case statement which has the function call.
From
hubert depesz lubaczewski
Date:
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
Re: failed to execute the psql case statement which has the function call.
From
"David G. Johnston"
Date:
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.
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.
Re: failed to execute the psql case statement which has the function call.
From
"David G. Johnston"
Date:
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.
"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
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