473,321 Members | 1,669 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Function declaration for MSFTSql developer

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
4 2009
michaelb
534 Expert 512MB
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
michaelb
534 Expert 512MB
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
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
michaelb
534 Expert 512MB
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

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

Similar topics

2
by: Thomas Matthews | last post by:
Hi, I'm getting linking errors when I declare a variable in the global scope, but not inside a function. The declarations are the same (only the names have been changed...). class Book {...
21
by: Rob Somers | last post by:
Hey people, I read a good thread on here regarding the reason why we use function prototypes, and it answered most of my questions, but I wanted to double check on a couple of things, as I am...
3
by: Dennis Chang | last post by:
Hi all, I was reading about function pointers and came across something which intrigued me. K&R2 calls qsort (pg.119) within main as so: qsort( (void **) lineptr, 0, nlines-1, (int (*) (void...
6
by: grist2mill | last post by:
I want to create a standard tool bar that appears on all pages that is a control. The toolbar has a button 'New'. What I wolud like when the user clicks on 'New' depends on the page they are on. I...
11
by: Yelena Varshal via AccessMonster.com | last post by:
Hello, I have a problem with one of msaccess.exe API calls that work on my desctop but does not work on the laptop from within MS ACCESS. There is a lot of differences between 2 computers...
20
by: Christian Christmann | last post by:
Hi, in a benchmark I've found an uncommon use of a function. This is the simplified form: 1 int foo( int f ) 2 { 3 return f; 4 } 5
20
by: svata | last post by:
Hello there, after some time of pondering I come to some solution which would suit me best. Please correct, if I am wrong. Function has two parameters. A string array, better said a pointer to...
4
by: Paulo Matos | last post by:
Hi all, I'm trying to work out a parser for function declarations but it turns out that it is harder than I initially thought. I'm looking at 3rd Ed of Stroustrup, page 808. I'm trying to parse...
2
by: f rom | last post by:
----- Forwarded Message ---- From: Josiah Carlson <jcarlson@uci.edu> To: f rom <etaoinbe@yahoo.com>; wxpython-users@lists.wxwidgets.org Sent: Monday, December 4, 2006 10:03:28 PM Subject: Re: ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.