Thread: quoted_literal with numeric variable.

How to construct an 'EXECUTE' sentence into a function using numeric
variables?

To insert the variable in the string to execute, i use
'quote_literal(numero)' where numero is a numeric variable.

When I execute the function, i get this message:

    ERROR:  Function 'quote_literal(numeric)' does not exist
                Unable to identify a function that satisfies the given argument
types
                You may need to add explicit typecasts


There is the complet test code:

DROP FUNCTION prova();
DROP TABLE temp_tab;
CREATE LOCAL TEMP table temp_tab(
    t_nom        varchar(15) NOT NULL,
    t_num       numeric(4,0)
) WITH OIDS;
CREATE function prova() RETURNS VARCHAR AS
'
DECLARE
    nombre VARCHAR(15);
    numero NUMERIC(4,0);
    RESULT VARCHAR;
BEGIN
    result = ''ok'';
    nombre = ''pepe'';
    numero = 1;
    EXECUTE ''INSERT INTO temp_tab VALUES(''
                        || quote_literal(nombre) ||'',''
                        || quote_literal(numero) ||'')'';
    return result;
END
'
LANGUAGE 'plpgsql';


Any help will be greatly appreciated.

Conxita.




I

=?iso-8859-1?Q?Conxita_Mar=EDn?= <comarin@telefonica.net> writes:
> How to construct an 'EXECUTE' sentence into a function using numeric
> variables?
> To insert the variable in the string to execute, i use
> 'quote_literal(numero)' where numero is a numeric variable.

Don't bother with quote_literal.  You only need that for string values
that might contain quotes or backslashes.  A numeric variable won't have
either.

            regards, tom lane