Re: [GENERAL] Parser does not like %ROWTYPE in the RETURNS clause
ezra epstein wrote: Aother head banger for me. Below is a complete example of the code Using Postgres 7.4, the function test gets this: psql:temp3.sql:10: ERROR: syntax error at or near % at character 135 the function test2 gets this: psql:temp3.sql:10: ERROR: syntax error at or near ROWTYPE at character 141 Very odd. The first doesn't even like the '%' character -- perhaps because doof is a table type rather than a column (domain) type??? And when we schema qualify the name of the table then the % is ok, but ROWTYPE is not. Is this a well-known limitation or a new (7.4) bug? I tried combing the docs to no avail. Thanks, Ezra E. code /* CREATE TABLE doof ( pk_id serial ) WITHOUT OIDS; */ CREATE OR REPLACE FUNCTION test(INTEGER) RETURNS doof%ROWTYPE AS ' SELECT * FROM doof WHERE pk_id=$1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION test2(INTEGER) RETURNS public.doof%ROWTYPE AS ' SELECT * FROM doof WHERE pk_id=$1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; /code ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Try replacing the rowtype with SETOF doof: CREATE OR REPLACE FUNCTION test(INTEGER) RETURNS SETOF doof AS ' SELECT * FROM doof WHERE pk_id=$1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; Hope that helps. Ron ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Parser does not like %ROWTYPE in the RETURNS clause of a
Dear ezra epstein ; Using Postgres 7.4, the function test gets this: psql:temp3.sql:10: ERROR: syntax error at or near % at character 135 the function test2 gets this: psql:temp3.sql:10: ERROR: syntax error at or near ROWTYPE at character 141 Very odd. The first doesn't even like the '%' character -- perhaps because doof is a table type rather than a column (domain) type??? ROWTYPE for SQL Language you may please check that code /* CREATE TABLE doof ( pk_id serial ) WITHOUT OIDS; */ CREATE OR REPLACE FUNCTION test(INTEGER) RETURNS doof%ROWTYPE AS ' SELECT * FROM doof WHERE pk_id=$1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION test2(INTEGER) RETURNS public.doof%ROWTYPE AS ' SELECT * FROM doof WHERE pk_id=$1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; /code The above code gave error on mine system also PostgreSQL 7.3.4 what I think you want to something like this code CREATE OR REPLACE FUNCTION test2(INTEGER) RETURNS public.doof AS ' SELECT * FROM doof WHERE pk_id = $1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION test1(INTEGER) RETURNS doof AS ' SELECT * FROM doof WHERE pk_id = $1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; /code Mine Limited knowledge tells me that this is not a BUG but just an effect of thinking out of the box Shoot back if I was right please. Regards, Vishal Kashyap ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Parser: parse error - please help...
On Mon, Dec 22, 2003 at 04:24:52PM -0700, Andrew Ayers wrote:
INSERT INTO reg(customer number, company name, address, city, state,
zip, phone, alt_phone, fax, alt_fax, iaccess, browser, provider,
software, sversion, ynintegrated, ynnewtest, login, password, txtnetid,
alias_1, alias_2, url, approval_password, ynauthalert, loi_date,
contract_eff, contract_term, term_code, main_eff, main_term, live_eff,
renewal_eff, cs_rep, txtactmanager, txtsalesrep, txtprojmanager,
modem_number, dbc_users, intbillableusers, country, mmowarning,
mmositefileflags, verified, buildtimedate, ynhipaaba) VALUES ('855',
'Test Company', '1234 West Jazz Street', 'Tempe', 'AZ', '85203', '(602)
123-4567', '(602) 123-4567', '(602) 123-4567', '(602) 123-4567', true,
'Internet Explorer', DEFAULT, 'HP3.5', '13', false, false, 'eci855',
DEFAULT, DEFAULT, 'Test', 'Tester', 'http:\\www.eldocomp.com\', DEFAULT,
Note here:^^
false, DEFAULT, '09/11/2002', DEFAULT, DEFAULT, '09/11/2002', DEFAULT,
DEFAULT, DEFAULT, 'Chris Riesgraf ext. 244', 'Keri Daminelli ext. 246',
'Kerry Winkle ext. 229', DEFAULT, DEFAULT, DEFAULT,
'0', 'USA', 'afdsf', DEFAULT, DEFAULT, DEFAULT, false);
You have an escaped ', which will make the string literal continue till
the next '. You should really be escaping your \ and ' when they are
embedded in data. Consider something like Joh\n; it will insert Joh
followed by a newline.
Also, an URL with \ is quite weird ... I think they like / best.
--
Alvaro Herrera (alvherre[a]dcc.uchile.cl)
You liked Linux a lot when he was just the gawky kid from down the block
mowing your lawn or shoveling the snow. But now that he wants to date
your daughter, you're not so sure he measures up. (Larry Greenemeier)
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] parser
I had this problem a couple years ago. I changed the column name before the upgrade by renaming the table to table_old, creating a new table with the original table name by selecting from table_old. For the column I needed to rename, I selected offset as 'x_offset'. I made sure everything was there, then dropped table_old and exported the database prior to the upgrade. It's a bit of trouble to find all the queries in the software and replace the column name, but for my application it was workable. Or maybe you already know how to do this but don't think you should have to? I hope this helps. Sarah Officer officers Jeff MacDonald wrote: hi, got this question from a user, anyone up for some discussion on it 1) I have been using 6.3 and recently tried to move to 6.5. I found that the word "offset" is now a reserved word and I have used it as a field name in a table. 6.5 reports this as an error. I wonder if the parser should do this since the context is as a field name. == Jeff MacDonald [EMAIL PROTECTED] irc: bignose on EFnet ==
