From: Jasen Betts <jasen@xnet.co.nz>
To: pgsql-sql@postgresql.org
Subject: Re: MS-SQL Store Procedure to Postgresql Function
Date: 1 Feb 2012 10:52:41 GMT
Message-ID: <jgb5hp$6db$1@reversiblemaps.ath.cx> (raw)
References: <1327905771.73991.YahooMailNeo@web121606.mail.ne1.yahoo.com>
On 2012-01-30, Rehan Saleem <pk_rehan@yahoo.com> wrote:
>hi , how i can convert this store procedure to PostgreSQL function,
>especially I really dont know how to set type to readonly in PostgreSQL.
>thanksUserACUserAC
I don't actually understand T-SQL so I'm going from the
structure more than from the actual meaning of the code given
it looks like the same effect could be had in a different way
"delete ... using" instead.
this is assuming that you set up a cascading delete of
UserAccountDetails
assuming the thie makes sense:
ALTER TABLE UserAC ADD PRIMARY KEY(UserDataAcountId);
-- the important part is UserAC.UserDataAcountId has an index
-- with the unique property, I'm guessing id is actually a primary key.
ALTER TABLE UserAccountDetails ADD UserDataAcountId REFERENCES
UserAC(UserDataAcountId) ON DELETE CASCADE;
-- again i'm guessing from the names of the columns.
-- nut if that all fits your data, and usage patterns
-- you can do the following:
DELETE FROM UserAC USING /*some-query*/ WHERE /*some-condition*/;
and get pretty-much the same effect.
here /*some-query*/ would be your table expression and
/*some-condition*/ would join it to UserAC
postgresql treats all unquoted identifiers as lower case, if you are
have stuff with mixed-case names you need to quote them with double
quotes, I have assumend that the mixed case is just for menmonic
reasons and not significant.
although postgres doesn't do table parameters there are three
other possibilities,
dynamic sql
refcursors
agregate functions.
thses are all considered advanced topics,
expect EVERYTHING that is not covered by SQL standards to be totally
different,
there is usually a way to do what you want, it may well be completely
different to the old way.
--
⚂⚃ 100% natural
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasen@xnet.co.nz
Subject: Re: MS-SQL Store Procedure to Postgresql Function
In-Reply-To: <jgb5hp$6db$1@reversiblemaps.ath.cx>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox