cancel
Showing results for 
Search instead for 
Did you mean: 

strange behavior of function in edbspl with select into and var:=(select...)

Silver Adventurer

strange behavior of function in edbspl with select into and var:=(select...)

hi all, i have a function in edbspl  that have diferente behaivor with select into (return error) and variable:=(select ...) (return null)

 

for example:

CREATE OR REPLACE FUNCTION public.funcion_edb(p_valor numeric)
RETURNS character varying AS
$BODY$

mayus varchar2;
tempo varchar2;
BEGIN
select datname into tempo from pg_database where oid=p_valor;
mayus := upper(tempo);
RETURN mayus;

END$BODY$
LANGUAGE edbspl VOLATILE SECURITY DEFINER



CREATE OR REPLACE FUNCTION public.funcion_edb2(p_valor numeric)
RETURNS character varying AS
$BODY$

mayus varchar2;
tempo varchar2;
BEGIN
tempo:= (select datname from pg_database where oid=p_valor);
mayus := upper(tempo);
RETURN mayus;

END$BODY$
LANGUAGE edbspl VOLATILE SECURITY DEFINER



###return OK

testutf8=# select * from public.funcion_edb(1);
funcion_edb 
-------------
TEMPLATE1
(1 fila)



###no exits the database with oid 2 and

####here return error query returned no rows

testutf8=# select * from public.funcion_edb(2);
ERROR: query returned no rows
CONTEXTO: edb-spl function public.funcion_edb(numeric) line 6 at SQL statement
testutf8=# select * from public.funcion_edb2(1);
funcion_edb2 
--------------
TEMPLATE1
(1 fila)



###no exits the database with oid 2 and

####here return null

testutf8=# select * from public.funcion_edb2(2);
funcion_edb2 
--------------

(1 fila)

testutf8=#



is normal behaivor in edbspl function or a bug?

 

 

 

Nevertheless in function plpgsql alway the behaivor is the same 

create or replace FUNCTION public.funcion_plpgsql(p_valor numeric) RETURNs character varying as
$$
declare 

    mayus  varchar2;
    tempo varchar2;
    BEGIN
        select datname into tempo   from pg_database where oid=p_valor;
        mayus := upper(tempo);
        RETURN mayus;
   END;  
    $$
    language plpgsql 

 create or replace FUNCTION public.funcion_plpgsql2(p_valor numeric) RETURNs character varying as
$$
declare 
    mayus  varchar2;
    tempo varchar2;
    BEGIN
        tempo:= (select datname   from pg_database where oid=p_valor;
        mayus := upper(tempo);
        RETURN mayus;
   END;  
    $$
    language plpgsql 

 

testutf8=# select * from  public.funcion_plpgsql(2);
 funcion_plpgsql 
-----------------
 
(1 fila)

testutf8=# select * from  public.funcion_plpgsql2(2);
 funcion_plpgsql2 
------------------
 
(1 fila)

testutf8=# 

 

 

my verion is:

testutf8=# select version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 EnterpriseDB 10.5.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 fila)

testutf8=#

Regards

3 REPLIES
EDB Team Member

Re: strange behavior of function in edbspl with select into and var:=(select...)

Hi estolongo,

 

We have verified your issue and We are able to reproduce the issue. We will work with our team internally and get back to you on same.

 

Thanks and regards,

Ninad Shah

Silver Adventurer

Re: strange behavior of function in edbspl with select into and var:=(select...)

hi @nshah, thanks,

any news about it?

EDB Team Member

Re: strange behavior of function in edbspl with select into and var:=(select...)

Hi asotolongo,

 

We apologize for no response for long time. This behavior appears expected one. We may use NVL function to suppress the error. Revised query with NVL may look like below.

 

select nvl(datname,'') from pg_database where oid=p_valor

 

Thanks,

Ninad