ERROR: 42601: a column definition list is only allowed for functions returning record

Since the postgres forums appear to be a little quite, I'll post my error here as well. 

This error is in regards to the postgres .net npgsql driver and seemed to only occur when using stored procs.

The Error
The behavior I experienced happens when using stored procedures started out with me getting the following error:
ERROR: 42601: a column definition list is only allowed for functions returning "record"

After checking and double checking the stored proc and the parameters I was sending in I turned Npgsql's debugging on. The error appears to be coming from a statement that looks like the following:
select * from sitemenus_ins(68::int4,34::int4) as (psitemenuid int4, prowstamp timestamp )
This line of sql fails. 

However the following will work:
select * from sitemenus_ins(68::int4,34::int4)
The only difference being that the second one has the 'as(...' clause taken off.

Strange Observation:
The snippet in question is executed automatically by the provider after the NpgsqlCommand.cs class does some other queries to try and match the list of the parameters that are in the stored procedure it is about to execute. However, the 'select' it uses to determine if it should append the 'as(..' clause is queried from the pg_proc table but is matched on the parameter types that are passed in, and not on those of the actual proc.

For example, this means that if I passed in say a 'Text' type (25) as opposed to the defined datatype on a proc (eg. 'Varchar' 1043) The select will fail and the extra 'as (...' on the end of the statement in question will not be appended. So the function call will work

Resolution:
I have no idea what the ideal solution is, I don't have a firm understanding of the ins and outs of the provider, but after downloading the source and removing the check so the 'as (...' never gets appended, all CRUD operations in my unit tests pass.

Another suggestion by Al is that maybe it is a piece of code left in there that was needed from a previous version of postgres (since the code has been around for a few years now).  Whatever the reason, it causes errors, I've checked the source for another postgres provider and it doesn't attempt to do the append.  The only concern I have if this is an actual bug is that it looks as though the same code has been copied into the branch for the npgsql2 provider, so those wanting to use stored procs on postgres in the future may find the same error I have.

Posted by: Brendan Kowitz
Last revised: 21 Sep 2013 12:15PM

Comments

3/14/2010 9:06:28 AM
Was the function returning a table?

If so, then a better workaround is to use commandtype text and query as 'SELECT * FROM sitemenus_ins(..)'. This will avoid the appending of '..as..' statement.

No new comments are allowed on this post.