By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,985 Members | 1,694 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,985 IT Pros & Developers. It's quick & easy.

Function declaration for MSFTSql developer

P: 4
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

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE author
  2. (
  3.   "authorName" character varying(100) NOT NULL,
  4.   "authorID" integer NOT NULL DEFAULT nextval('"author_authorID_seq"'::regclass),
  5.   CONSTRAINT "PK_authorID" PRIMARY KEY ("authorID"),
  6.   CONSTRAINT "UK_authorID" UNIQUE ("authorID")
  7.  
  8. CREATE TABLE book
  9. (
  10.   "bookID" integer NOT NULL DEFAULT nextval('"book_bookID_seq"'::regclass),
  11.   "bookName" character varying(100) NOT NULL,
  12.   "authorID" integer NOT NULL,
  13.   CONSTRAINT book_pkey PRIMARY KEY ("bookID"),
  14.   CONSTRAINT "book_authorID_fkey" FOREIGN KEY ("authorID")
  15.       REFERENCES author ("authorID") MATCH SIMPLE
  16.       ON UPDATE RESTRICT ON DELETE RESTRICT,
  17.   CONSTRAINT "U_bookID" UNIQUE ("bookID")
  18.  
  19. create type t_book_author as ( bookID integer, authorID integer,
  20.     bookName varchar, authorName integer );
  21.  
  22. CREATE OR REPLACE FUNCTION testFunc( bID integer )
  23. RETURNS SETOF t_book_author AS $$
  24. begin
  25.     select B.bookID, B.authorID, B.bookName, A.authorName
  26.     from book B
  27.     inner join author A on A.authorID = B.authorID
  28.     where B.bookID = bID;
  29.  
  30. end;
  31. $$ LANGUAGE plpgsql;
  32.  
Now, when I try to run the function:

Expand|Select|Wrap|Line Numbers
  1. select testFunc( 2 )
  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
Jun 6 '07 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 534
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?
Jun 10 '07 #2

Expert 100+
P: 534
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:
Expand|Select|Wrap|Line Numbers
  1. select B."bookID", B."authorID", B."bookName", A."authorName"  ... ...
  2.  
Jun 10 '07 #3

P: 4
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?
Jun 29 '07 #4

Expert 100+
P: 534
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.
Jul 1 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.