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:
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
4 2009
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?
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" ... ...
-
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?
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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
{...
|
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...
|
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...
|
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...
|
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...
|
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
|
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...
|
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...
|
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: ...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |