[SQL] Data layer migration from MSSQL
Hi, I'm working on migrating a data layer from MS SQL Server to PostgreSQL 8.2 and found that some reserved words should only be written between quotes and thus are case sensitive (actually only happened with a table field named "order"). Is there any way to bypass this case sensitivity or at least determine that I am going to use certain reserved words as table fields (or any other possible solutions) ? Thanks, Sebastian
Re: [SQL] Data layer migration from MSSQL
I think the grammer should help the parser to determine what you mean when the token ORDER is seen. for example in a select statement... Syntax: SELECT expression [, ...] ... [ FROM from_item [, ...] ] [ WHERE condition ] ... [ ORDER BY expression [ ASC | DESC | USING operator ] keywords (or reserved words) should not be placed in quotes because the parser relies on them to steer ... Cheers Medi On Thu, Feb 21, 2008 at 9:47 AM, Sebastian Rychter <[EMAIL PROTECTED]> wrote: > Hi, I'm working on migrating a data layer from MS SQL Server to > PostgreSQL 8.2 and found that some reserved words should only be written > between quotes and thus are case sensitive (actually only happened with a > table field named "order"). Is there any way to bypass this case > sensitivity or at least determine that I am going to use certain reserved > words as table fields (or any other possible solutions) ? > > > > Thanks, > > > > Sebastian >
Re: [SQL] Data layer migration from MSSQL
Sorry, I think it was misunderstood. I meant that I used the keyword “Order” as a table property, not as part as the statement In MS SQL I use [Order] and then just query : Select [Table1].[Order] from [Table1] Or Select [Table1].[order] from [Table1] In Postgresql I can’t do : create table Table1 (order varchar(10)) -- because “order” is a keyword I have to do it like : create Table1 (“Order” varchar(10)) And then always do the query like : Select “Order” from Table1 which is different from Select “order” from Table1 Thanks again. De: Medi Montaseri [mailto:[EMAIL PROTECTED] Enviado el: jueves, 21 de febrero de 2008 16:43 Para: Sebastian Rychter CC: pgsql-sql@postgresql.org Asunto: Re: [SQL] Data layer migration from MSSQL I think the grammer should help the parser to determine what you mean when the token ORDER is seen. for example in a select statement... Syntax: SELECT expression [, ...] ... [ FROM from_item [, ...] ] [ WHERE condition ] ... [ ORDER BY expression [ ASC | DESC | USING operator ] keywords (or reserved words) should not be placed in quotes because the parser relies on them to steer ... Cheers Medi On Thu, Feb 21, 2008 at 9:47 AM, Sebastian Rychter <[EMAIL PROTECTED]> wrote: Hi, I'm working on migrating a data layer from MS SQL Server to PostgreSQL 8.2 and found that some reserved words should only be written between quotes and thus are case sensitive (actually only happened with a table field named "order"). Is there any way to bypass this case sensitivity or at least determine that I am going to use certain reserved words as table fields (or any other possible solutions) ? Thanks, Sebastian
Re: [SQL] Proposed archival read only trigger on rows - prevent history modification
On Tuesday 12 February 2008 03:25, Bryce Nesbitt wrote:
> Yes, the view approach has some advantages. But it still leaves the
> underlying tables naked to modification.
> And since the most likely error is... well... me (or another admin) at
> the SQL prompt, we want underlying tables protected also.
>
> chester c young wrote:
> > instead of triggers I use update-able views and permissions.
> >
> > 1. all dml goes through the view
> > 2. use rules on the view to do dml to the table
> > 3. in rules prevent updating all/any columns when whatever
> > 4. grant dml to view to your pgconnect user
> > 5. revoke dml from table to your pgconnect user
> >
> > imho another instance where rules rule. for example, you can easily
> > fit logging into the same view.
You can use rules on the underlying tables themselves (on update do nothing)
which tends to work pretty well. You can also add triggers into the mix to
raise errors on update. Also dont forget to revoke update/delete/install
privileges as appropriate. And look into vacuum freeze.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Proposed archival read only trigger on rows - prevent history modification
On Tuesday 12 February 2008 03:25, Bryce Nesbitt wrote: > Yes, the view approach has some advantages. But it still leaves the > underlying tables naked to modification. And since the most likely > error is... well... me (or another admin) at the SQL prompt, we want > underlying tables protected also. Couldn't you make a row trigger BEFORE UPDATE? CREATE OR REPLACE FUNCTION readonly_handler() RETURNS "trigger" AS $BODY$ BEGIN IF OLD.read_only = TRUE THEN RETURN OLD; ELSE RETURN NEW; END IF; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; THINK BEFORE YOU PRINT - Save paper if you don't really need to print this ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments e-mail. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
