postgresql - how to send a parameter as a table in a function?

Question

I am using postgresql, I would like to know how to send a parameter as a table in a function.

I have already created the type type_detalle_ac:

CREATE TYPE public.type_detalle_ac AS
(
 id_componente integer,
 precio numeric(10,2),
 cantidad integer,
 sub_total numeric(10,2)
);

and I use it in the following function:

CREATE OR REPLACE FUNCTION public.sp_adm_artefacto(
v_serie character varying DEFAULT NULL::character varying,
v_tipo_artefacto integer DEFAULT NULL::integer,
v_modelo character varying DEFAULT NULL::character varying,
v_marca integer DEFAULT NULL::integer,
v_detalle type_detalle_ac DEFAULT NULL::type_detalle_ac,
v_usuario integer DEFAULT NULL::integer,
v_id_artefacto integer DEFAULT NULL::integer,
v_tipo_operacion character DEFAULT NULL::bpchar)
RETURNS void
LANGUAGE 'plpgsql'

COST 100
VOLATILE 
ROWS 0
AS $BODY$

DECLARE 

    VL_ID_ARTEFACTO INTEGER;
BEGIN
    IF V_TIPO_OPERACION = 'I' THEN

        INSERT INTO DETALLE_AC(ID_DETALLE_AC,ID_ARTEFACTO,ID_COMPONENTE,PRECIO,CANTIDAD,SUB_TOTAL,
                               USUARIO_CREACION,FECHA_CREACION)
        SELECT 1,1,ID_COMPONENTE,PRECIO,CANTIDAD,SUB_TOTAL,v_usuario,NOW() FROM V_DETALLE;

    END IF;

END;


$BODY$;

But when calling the function:

SELECT "sp_adm_artefacto"('1321321',1,'F-14',1,(1,10,5,50),1,NULL,'I')

I get an error :

ERROR:  no existe la relación «v_detalle»
LINE 3: ...NTE,PRECIO,CANTIDAD,SUB_TOTAL,v_usuario,NOW() FROM V_DETALLE

Thank you

It seems the errors says the v_detalle table doesn't exist and it's not about the type. Do you have such table? — kaveh
– kaveh, Commented Mar 2, 2018 at 19:08

Laurenz Albe · Accepted Answer · 2018-03-02 19:52:24Z

1

v_detalle is not a table, so you cannot use it in the FROM clause of a query.

You should write

INSERT INTO detalle_ac (...)
VALUES (1, 1, v_detalle.id_componente, v_detalle.precio, ...);

answered Mar 2, 2018 at 19:52

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abelisto · Accepted Answer · 2018-03-03 19:04:11Z

0

There are five sources of data in the FROM clause:

Table
View
Values (select * from (values(1,'a'),(2,'b')) as t(x,y);)
Set returning functions (link)
Subqueries

For your question: Laurenz already answered about values way

Yet another way, shorter and more general but probably slower a bit:

select * from (select variable.*) as t;

It transforms your variable to table-like subquery.

answered Mar 3, 2018 at 19:04

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Collectives™ on Stack Overflow

postgresql - how to send a parameter as a table in a function?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related