By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,835 Members | 2,305 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,835 IT Pros & Developers. It's quick & easy.

Obtain boolean value of expression in PLPGSQL

P: n/a
Hello

My task is to validate expression and get value of it in boolean variable.
I try to do following:
CREATE OR REPLACE FUNCTION get_value(integer) RETURNS boolean AS'
DECLARE
Ret boolean;
Op TEXT = ''='';
BEGIN

Ret := 3 || Op || $1;
RETURN Ret;
END;
' language 'plpgsql';

when I try to execute this function I get such result:

bill=# select get_value(3);
WARNING: Error occurred while executing PL/pgSQL function get_value
WARNING: line 6 at assignment
ERROR: Bad boolean external representation '3=3'
bill=#

So, how can I explain that resulting type must be boolean ? Also, I try to
play with SELECT INTO with same result.

--
.... All opinions expressed are mine and not those of my employer.

Yours, Max [Msg N 2419]
-------------------------------------------
mailto: mx@lucky.net phone: +380-44-2054455

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Tuesday 04 November 2003 10:06, Max Speransky wrote:
Hello

My task is to validate expression and get value of it in boolean variable.
I try to do following: Ret := 3 || Op || $1;
RETURN Ret; bill=# select get_value(3);
WARNING: Error occurred while executing PL/pgSQL function get_value
WARNING: line 6 at assignment
ERROR: Bad boolean external representation '3=3'


The problem is that your expression is being evaluated as a string. In the
absence of an eval() function, you probably should do something like:

my_stmt := ''SELECT '' || 3 || Op || $1;
EXECUTE my_stmt;
FOR EACH ...

So - build a simple query, execute it and read the result. Should do what you
want.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #2

P: n/a
Hello

You are on wrong way.

Try like

DECLARE
b RECORD;
s TEXT;
op TEXT;
BEGIN op := ''='';
s := ''SELECT 3 ''|| op || ''|| $1 || ''AS b'';
FOR b IN EXECUTE s LOOP
RETURN b.b;
END LOOP;
END;

Regards
Pavel
On Tue, 4 Nov 2003, Max Speransky wrote:
Hello

My task is to validate expression and get value of it in boolean variable.
I try to do following:
CREATE OR REPLACE FUNCTION get_value(integer) RETURNS boolean AS'
DECLARE
Ret boolean;
Op TEXT = ''='';
BEGIN

Ret := 3 || Op || $1;
RETURN Ret;
END;
' language 'plpgsql';

when I try to execute this function I get such result:

bill=# select get_value(3);
WARNING: Error occurred while executing PL/pgSQL function get_value
WARNING: line 6 at assignment
ERROR: Bad boolean external representation '3=3'
bill=#

So, how can I explain that resulting type must be boolean ? Also, I try to
play with SELECT INTO with same result.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #3

P: n/a
Max Speransky said:
Hello

My task is to validate expression and get value of it in boolean variable. [snip] when I try to execute this function I get such result:

bill=# select get_value(3);
WARNING: Error occurred while executing PL/pgSQL function get_value
WARNING: line 6 at assignment
ERROR: Bad boolean external representation '3=3'


|| is the string concatenation operator - so your expression creates a
string "3=3" and then tries to return that as a boolean hence the error.

if you cannot just do "return 3 = $1;" perhaps you could try using EXECUTE?

cheers
Martin
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.