call function postgresql 9.5 - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5) +--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24) +--- Thread: call function postgresql 9.5 (/showthread.php?tid=63957) |
call function postgresql 9.5 - andresweb - 12-27-2015 I have not found help. Now I created a function in postgresql 9.5 CREATE OR REPLACE FUNCTION certificado_notas() RETURNS TABLE(mat_anio_academico integer, udi_id integer, udi_nombre character varying, sem_codigo character, udi_letra_nomina character, mud_nota_final integer, mud_nora_recuperacion integer, mma_id integer) AS $BODY$ BEGIN RETURN QUERY SELECT m.mat_anio_academico, mu.udi_id, u.udi_nombre, u.sem_codigo, u.udi_letra_nomina, mu.mud_nota_final, mu.mud_nota_recuperacion, m.mma_id FROM (SELECT DISTINCT ON (mud.udi_id) mud.mud_id FROM matricula_unidad_didactica AS mud INNER JOIN matricula AS m ON mud.mat_id=m.mat_id INNER JOIN alumno AS a ON m.alu_id=a.alu_id WHERE a.alu_id = 415 ORDER BY mud.udi_id DESC, m.mat_anio_academico DESC ) AS mudi JOIN matricula_unidad_didactica AS mu ON mudi.mud_id=mu.mud_id JOIN unidad_didactica AS u ON mu.udi_id=u.udi_id JOIN matricula AS m ON mu.mat_id=m.mat_id JOIN alumno AS a ON m.alu_id=a.alu_id WHERE a.alu_id = 415 ORDER BY u.sem_codigo ASC, u.udi_letra_nomina ASC, m.mat_anio_academico ASC; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; attempt to call in 2 ways. $this->db->query("SELECT certificado_notas()"); $this->db->call_function("certificado_notas"); in both no error. as I can call a function in codeigniter postgresql RE: call function postgresql 9.5 - andresweb - 12-27-2015 I tried with a simpler function. Code: DROP FUNCTION sp_1() pgadmin3 use it in this way: Code: SELECT codigo, nombre FROM sp_1(); so also Code: SELECT * FROM sp_1(); and works. codeigniter but does not work. Code: $this->db->query('SELECT codigo, nombre FROM sp_1()'); or Code: $this->db->query('SELECT * FROM sp_1()') the error is ERROR: SELECT * with no tables specified is not valid LINE 1: SELECT * ^ SELECT * |