[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    [SQL] SRF_RETURN_NEXT Error: rows returned by function are not all of the same row type
From:       amul sul <sul_amul () yahoo ! co ! in>
Date:       2014-02-24 6:47:28
Message-ID: 1393224448.45554.YahooMailNeo () web193503 ! mail ! sg3 ! yahoo ! com
[Download RAW message or body]

Hi ALL,

I need little help, in writing C function returns multiple row. (please find attached \
c code: show_eudc.c)

I have created table & inserted values in "temp"  as: 

create table temp(id int, value varchar);
insert into temp select x, lpad('string', 10, x::text) from generate_series(1, 10) x;

Just want to return few rows  SELECT * FROM temp using C function;

While  setting funcctx->max_calls = 1 work as expected;  

postgres=# select * from show_eudc();
NOTICE:  call_cntr : 0
 f1 |     f2
----+------------
  1 | 1111string
(1 row)

but setting funcctx->max_calls = 2;(or 4) throws error.

NOTICE:  call_cntr : 0
NOTICE:  call_cntr : 1
ERROR:  rows returned by function are not all of the same row type

I am not getting cause of error, as it working for single call, but why not for \
multiple call?

Do i missing something?

Thanks in Advance 


P.S. more info:

Loaded C function as:

CREATE OR REPLACE FUNCTION show_eudc(OUT f1 integer, OUT f2 varchar)
RETURNS SETOF record
AS 'MODULE_PATHNAME', 'show_eudc'
LANGUAGE C IMMUTABLE STRICT;


Regards,
Amul Sul


["show_eudc.c" (application/octet-stream)]

/*
 * =====================================================================================
 *
 *       Filename:  test.c
 *
 *    Description:  function returning multiple row
 *        Version:  1.0
 *        Created:  02/10/2014 02:52:22 PM
 *       Revision:  none
 *       Compiler:  gcc
 *
 *         Author:  Amul Sul
 *
 * =====================================================================================
 */

#include "postgres.h"
#include "funcapi.h"
#include "executor/spi.h"

#define sql "select * from temp limit 4"

Datum show_eudc(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(show_eudc);

Datum
show_eudc(PG_FUNCTION_ARGS)
{
	FuncCallContext *funcctx;
	int call_cntr;
	int max_calls;
	
	HeapTupleHeader  values[4];

	/*  Stuff done only on the first call of the function. */
	if (SRF_IS_FIRSTCALL()) {
		MemoryContext oldcontext;

		int ret, i;
		TupleDesc tupdesc;
		SPITupleTable *tuptable = NULL;
		
		/*  create a function context for cross-call persistence */
		funcctx = SRF_FIRSTCALL_INIT();
		funcctx->max_calls = 2;

		/*  switch to memory context appropriate for multiple function calls */
		oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);

		SPI_connect();

		ret = SPI_exec(sql, 0);
		
		if (ret == SPI_OK_SELECT) {
			tupdesc = SPI_tuptable->tupdesc;
			tuptable = SPI_tuptable;
			for(i=0; i < 4; i++)
				values[i] = SPI_returntuple(tuptable->vals[i], tupdesc);
		}
	
		/*  Build a tuple descriptor for our result type */
		if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) {
			ereport(ERROR,
					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
					errmsg("function returning record called in context "
							"that cannot accept type record")));
		}

		MemoryContextSwitchTo(oldcontext);

	}/* End if(SRF_IS_FIRSTCALL) */

	/*  stuff done on every call of the function */
	funcctx = SRF_PERCALL_SETUP();

	call_cntr = funcctx->call_cntr;
	max_calls = funcctx->max_calls;

	if (call_cntr < max_calls) {

		elog(NOTICE, "call_cntr : %d", call_cntr);

		SRF_RETURN_NEXT(funcctx, values[call_cntr]);
	} else {
		/*  Do when there is no more left. */
		SPI_finish();
		SRF_RETURN_DONE(funcctx);
	} 
	
}/* end show_res */ 


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


[prev in list] [next in list] [prev in thread] [next in thread]