Re: [GENERAL] [ask] Return Query

[ataherster]

thanks Tom Lane, my problem resolved, I'm trying to re-create my table and 
function, and this working well

Tom Lane wrote:


ataherster  writes:
  
... but this function is not work with this error : 
ERROR:  structure of query does not match function result type

CONTEXT:  PL/pgSQL function "penjualan" line 6 at RETURN QUERY



This looks like a known limitation in plpgsql: it's not very good with
rowtypes that contain dropped columns.  Have you dropped some columns
in table PENJUALAN?  If so, try remaking the table from scratch.

regards, tom lane

  



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Fwd: Re: [GENERAL] [ask] Return Query]

[ataherster]

--- Begin Message ---

thanks Justin, my problem resolved, but i trying to re-create table and 
function, i'm using PGAdmin III to Create Script
table and function before delete my table and function, after that I run Create 
Sript without change my script
and working well

my question is : whether that bug in postgreSQL? 



I feel afraid if will happen again like this by itself

thanks before for your help

Note : I'm sory about my english, my english is very bad


Justin wrote:


ataherster wrote:
  

hai all, i'm trying create function like this

CREATE OR REPLACE FUNCTION penjualan(idcb integer)

 RETURNS SETOF penjualan AS


but this function is not work with this error : ERROR:  structure of 
query does not match function result type

CONTEXT:  PL/pgSQL function "penjualan" line 6 at RETURN QUERY

on the time i try with other table and working well

thanks for your help 

This is because Postgresql does  know the structure of the data to be 
returned.  So the choice either use OUT command  like so  
http://www.postgresql.org/docs/current/static/plpgsql-declarations.html
  
Create or Replace Function penjualan(idcb, integer, OUT f1 text, OUT f2 
integer) Returns SETOF penjualan AS
  
Or create a new data type describing the data structure   
http://www.postgresql.org/docs/8.3/static/sql-createtype.html
  
Create Type myTable as ( f1 text, f2 integer)
  
Create or Replace Function penjualan(idcb, integer) Returns SETOF 
myTable  AS
  
  
  



--- End Message ---

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] [ask] Return Query

[ataherster]

hai all, i'm trying create function like this

CREATE OR REPLACE FUNCTION penjualan(idcb integer)
 RETURNS SETOF penjualan AS
$BODY$
BEGIN

IF ($1 IS NULL) THEN
 return query SELECT * FROM PENJUALAN;
ELSE
 return query SELECT * FROM PENJUALAN WHERE IDCABANG=$1;
END IF;

END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE
 COST 100
 ROWS 1000;

but this function is not work with this error : 
ERROR:  structure of query does not match function result type

CONTEXT:  PL/pgSQL function "penjualan" line 6 at RETURN QUERY

on the time i try with other table and working well

thanks for your help



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general