Hi all,
If I have a simple set returning function like 'aafunc1'
----------------------------------------------------------------
CREATE OR REPLACE FUNCTION "public"."aafunc1" (v_acc integer) RETURNS SETOF text AS
$body$
BEGIN
RETURN NEXT 'Arbitary_string of unlimitedLength.';
RETURN NEXT 'Arbitary_string of unlimitedLength222222.';
RETURN;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
----------------------------------------------------------------
Which I then call using vbsrcipt, ADO & the postgresql odbc driver like this:-
----------------------------------------------------------------
Set oCmd = CreateObject("ADODB.Command")
With oCmd
.ActiveConnection = oConnection
.CommandType = 4 'adCmdStoredProc
.CommandText = "public.aafunc1"
.Parameters.Refresh()
.Parameters("v_acc").Value = 1
.Execute
End With
wscript.echo oCmd.Parameters(0)
----------------------------------------------------------------
Why does it not work, it returns an error ...
errmsg='ERROR: set-valued function called in context that cannot accept a set'
When looking at the odbc log
[3352]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=30, stmt='{ ? = call
public.aafunc1(?)}'
[3352]ResolveOneParam: from(fcType)=-16, to(fSqlType)=4
[3352] stmt_with_params = 'SELECT public.aafunc1(1) '
[3352] it's NOT a select statement: stmt=1c62230
[3352]send_query(): conn=1c639b0, query='SELECT public.aafunc1(1) '
[3352]send_query: done sending query
How come it is not picking up that this function returns a SETOF something, and then changing the
Sql statement to "'SELECT * from public.aafunc1(1) '"
I am using windows XP and postgres ODBC driver 8.02.00.02, and the postgres dbase 8.1.4 is running on linux.
I would appreciate some help/thoughts on this issue
Thanks...
Rodney Franks
