Function declaration for MSFTSql developer

[UngaWunga]

Ok, I'm pretty good with MsftSQL, but there's enough differences with PostgreSQL to make things confusing.

I'm having a problem declaring a function (I want a stored proc). Here's my schema

CREATE TABLE author
(
  "authorName" character varying(100) NOT NULL,
  "authorID" integer NOT NULL DEFAULT nextval('"author_authorID_seq"'::regclass),
  CONSTRAINT "PK_authorID" PRIMARY KEY ("authorID"),
  CONSTRAINT "UK_authorID" UNIQUE ("authorID")
) 
 
CREATE TABLE book
(
  "bookID" integer NOT NULL DEFAULT nextval('"book_bookID_seq"'::regclass),
  "bookName" character varying(100) NOT NULL,
  "authorID" integer NOT NULL,
  CONSTRAINT book_pkey PRIMARY KEY ("bookID"),
  CONSTRAINT "book_authorID_fkey" FOREIGN KEY ("authorID")
      REFERENCES author ("authorID") MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT "U_bookID" UNIQUE ("bookID")
) 
 
create type t_book_author as ( bookID integer, authorID integer,
    bookName varchar, authorName integer );
 
CREATE OR REPLACE FUNCTION testFunc( bID integer )
RETURNS SETOF t_book_author AS $$
begin
    select B.bookID, B.authorID, B.bookName, A.authorName
    from book B
    inner join author A on A.authorID = B.authorID
    where B.bookID = bID;
 
end;
$$ LANGUAGE plpgsql;
 

Now, when I try to run the function:

select testFunc( 2 )
 

I get this error, which makes no sense to me at all.

ERROR: column a.authorid does not exist
SQL state: 42703
Context: PL/pgSQL function "testfunc" line 2 at SQL statement

Any ideas? Thanks

[michaelb]

The first thing that caught my eye is that type t_book_author is defined as
(integer, integer, varchar, integer );
but what you actually select is
(integer, integer, varchar, varchar)

Can you correct this disagreement and see if it fixes the problem?

[michaelb]

Sorry, I could not think straight, but something made me come back and take another look at this posting.

My previous comment probably still holds, but nature of your problem is quite different.
When you created your tables you included column names in double-quotes, meaning you used a case-sensitive syntax. Therefore you must use case-sensitive syntax when referring to these columns in your queries, whether it is a plain SQL or a function.
For most people using case-sensitive names brings nothing but trouble.
Had you created your table with the same field names, but without double quotes, you would be able to address columns using case-insensitive syntax.

I think if you change your syntax to use case sensitive names it'll take care of your problem:

select B."bookID", B."authorID", B."bookName", A."authorName"  ... ...
 

[UngaWunga]

Thanks, that makes complete sense.

Quick question, though. I'm using pgAdmin III to create my tables, and there doesn't seem to be a way to specify case insensitivity. Any ideas? Are there better admin tools out there for pgSql?

[michaelb]

My advise - stay away from it.
There are very few scenarios where case sensitive names for tables or columns help, more often this hurts because you have to watch your code and put all those names in double-quotes.
By the same token you should be able to create database objects with case sensitive names if you include names in double-quotes.