Обсуждение: Obtain boolean value of expression in PLPGSQL

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

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

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.
>
>

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