Thursday, April 20, 2006

scalar function


MS SQL:

CREATE FUNCTION FormatterNumber(@i INT) RETURNS VARCHAR(100)
AS
BEGIN
RETURN CONVERT(VARCHAR, @i) + ' Michael the INT'
END
GO
SELECT DBO.FormatterNumber(1)







PostgreSQL: Note postresql parameter can be also be referenced by their parameter number, e.g. $1, $2..





-- we don't have to declare varchar(100) in returns clause,
-- it is adjusted dynamically

-- method 1, this is flexible, can have multiple statements between begin and end
CREATE FUNCTION FormatterNumber(m_I INT) RETURNS VARCHAR
AS
$$
BEGIN
RETURN m_I::VARCHAR || ' INT';
-- RETURN $1::VARCHAR || ' INT'; -- this will work too
END;
$$
LANGUAGE 'plpgsql';



-- method 2, just change the language 'plpgsql' to 'sql',
-- this is rigid, only one statement is allowed,
-- though seems this is much faster(haven't yet benchmark against method 1)

-- somehow the disadvantage of language 'sql' is you can't refer the paramater
-- by its name, only by parameter position

CREATE FUNCTION FormatterNumber(m_I INT) RETURNS VARCHAR
AS
$$
SELECT $1::VARCHAR || ' Michael the INT';
$$
LANGUAGE 'sql';



invocation sample:

SELECT FormatterNumber(1);






MS SQL:

CREATE FUNCTION FormatterDate(@d DATETIME) RETURNS VARCHAR(100)
AS
BEGIN
RETURN CONVERT(VARCHAR, @d) + ' Michael the date'
END
GO

SELECT DBO.FormatterDate('November 5 2006')


PostgreSQL:



-- method 1
CREATE OR REPLACE FUNCTION FormatterDate(m_D DATE) RETURNS VARCHAR
AS
$$
BEGIN
RETURN m_D::VARCHAR || ' Michael the date';
END;
$$
LANGUAGE 'plpgsql';



-- method 2
CREATE OR REPLACE FUNCTION FormatterDate(m_D DATE) RETURNS VARCHAR
AS
$$
SELECT $1::VARCHAR || ' Michael the date';
$$
LANGUAGE 'plpgsql';


SELECT FormatterDate('November 5 2006')




PostgreSQL is much flexible in its function, you can overload function name,
you can have two function with same name,
resulting to a more symmetrical usage of function





-- method 1
CREATE OR REPLACE FUNCTION Formatter(m_I INT) RETURNS VARCHAR
AS
$$
BEGIN
RETURN m_D::VARCHAR || ' Michael the INT';
END;
$$
LANGUAGE 'plpgsql';



-- method 2
CREATE OR REPLACE FUNCTION Formatter(m_D DATE) RETURNS VARCHAR
AS
$$
SELECT $1::VARCHAR || ' Michael the date';
$$
LANGUAGE 'plpgsql';



SELECT Formatter(1);
SELECT Formatter('nov 5 2006');


25 Comments:

Anonymous Anonymous said...

What a great resource!

1:13 PM

 
Anonymous Anonymous said...

http://cinecloudfilms.com/index.php?do=/blog/92473/there-m-a-r-c-b-y-m-a-r-c-j-a-c-o-b-s-%E3%83%90-%E3%83%83-%E3%82%B0-is-presently-only-one-option-fo/
http://ludhianacity.net/blog/57192/three-mobile-phone-deals/
http://www.soberhood.org/node/125734
http://sns.cam111.com/blogs/entry/There-are-umpteen-options-available-to-the-residents-of-UK-when-it-comes-to-mobile-connectivity
http://xmyishang.exblog.jp/17225972
http://www.mymarburg.com/blog/76812/and-%E3%83%9E-%E3%83%BC-%E3%82%AF-%E3%82%B8-%E3%82%A7-%E3%82%A4-%E3%82%B3-%E3%83%96-%E3%82%B9-%E3%83%90-%E3%83%83-%E3%82%B0-it-does-that-by/
http://www.bloglog.com/blog/fashion4/125905/it-039-ll-be-tough-and-it-039-ll-be-bright-yellow
http://www.benches2swings.com/vocab/catpath/gamesinteractive-design.html
http://developinglinks.com/index.php?do=/blog/133975/having-too-many-spyware-removers-or-running-on-your-system-actually-could-b/
http://www.clickprofesor.com/index.php?do=/blog/47656/when-you-use-a-spyware-remover/
http://tiredoftalk.org/jcow/index.php?p=blogs/viewstory/741
http://prsites.biz/myblog-admin/most-people-suspect-the-printer-as-soon-as-these-problems-crop-up.html
http://hollar.se/index.php?do=/blog/2283/the-spyware-automatically-puts-the-infected-files-in-a-category-and-saves-i/
http://heraldbulletin.neighborsink.com/node/247694
http://www.benches2swings.com/vocab/catpath/implementation-u-g-g-%E3%83%96-%E3%83%BC-%E3%83%84-%E9%80%9A-%E8%B2%A9-network-will-begin-later-month.html

5:08 AM

 
Anonymous Anonymous said...

http://www.moselbook.de/index.php?p=blogs/viewstory/144328
http://aaa333333.cocolog-nifty.com/blog/2013/01/which-is-pictbr.html
http://ludhianacity.net/blog/57433/choose-spyware-protection-prevent-spyware/
http://www.bloglog.com/blog/fashion4/125905/it-039-ll-be-tough-and-it-039-ll-be-bright-yellow
http://prsites.biz/myblog-admin/most-spybot-will-attack-your-computer-while-you-are-surfing-the-net.html
http://aaa333333.cocolog-nifty.com/blog/2013/01/the-called-part.html
http://100persenampuh.com/index.php?p=blogs/viewstory/2458
http://www.benches2swings.com/vocab/catpath/mobile-phones-double-digital-cameras-are-not-uncommon-any-more.html
http://huangshumei.cocolog-nifty.com/blog/2013/01/these-days-ever.html
http://a4gn.com/blogs/viewstory/16671
http://d.hatena.ne.jp/rrrtt/20130128/1359357923
http://www.bloglog.com/blog/xmyshang6/126030/the-technology-in-question-m-a-r-c-b-y-m-a-r-c-j-a-c-o-b-s-is-wi-fi
http://sns.cam111.com/blogs/entry/emergence-of-new-market-players-and-innovations
http://www.altasbaixarias.baumgarten.cnt.br/jcow/blogs/viewstory/87669
http://ludhianacity.net/blog/57194/more-then-one-spyware-remover-is-one-to-many/

8:07 AM

 
Anonymous Free Antivirus Download said...

I am interested in sql programs and these all are long. free antivirus download

2:04 AM

 
Anonymous Anonymous said...

Thanks for a marvelous posting! I seriously enjoyed reading it, you're a great author.I will remember to bookmark your blog and may come back later in life. I want to encourage that you continue your great writing, have a nice afternoon!

Feel free to visit my website; Depression

4:36 PM

 
Anonymous Anonymous said...

I just could not depart your site prior to suggesting that I
actually loved the usual info a person supply in your visitors?
Is going to be again frequently in order to check up
on new posts

my web blog; Vexxhost customer reviews

12:49 PM

 
Anonymous Anonymous said...

Hi to all, for the reason that I am in fact eager of reading this blog's post to be updated regularly. It includes good data.

my webpage cheapwebhostingfirms.com

3:18 PM

 
Anonymous Anonymous said...

Keep on writing, great job!

Feel free to surf to my weblog ... webhostingtop3.com

11:23 AM

 
Anonymous Anonymous said...

What’s Happening i am new to this, I stumbled upon this I've found It positively

useful and it has helped me out loads. I hope to contribute & help other users like its

aided me. Good job.

My weblog :: http://amartaxiservice.com/?q=node/52920

12:24 PM

 
Anonymous Anonymous said...

Thanks , I've recently been searching for info approximately this subject for a long time and yours is the best I've found out so far.
However, what about the bottom

line? Are you sure about the source?

Have a look at my blog post; www.diysolarheatingspain.com

12:11 AM

 
Anonymous Anonymous said...

When I initially commented I clicked the "Notify me when new comments are added" checkbox and now each time
a comment is added I get four emails with the same comment.
Is there any way you can remove people from that service?
Bless you!

Visit my page - Highly recommended Resource site

7:29 PM

 
Anonymous Anonymous said...

Hey there just wanted to give you a quick heads up and let you know a few of the

pictures aren't loading correctly. I'm not sure why but I think its a linking issue.
I've tried it in

two different browsers and both show the same outcome.

Visit my web-site - http://Www.Murciaproperty.net/

1:53 PM

 
Anonymous Anonymous said...

I have been browsing online more than 3 hours today, yet I
never found any interesting article like yours.
It is pretty worth enough for me. Personally,
if all site owners and bloggers made good content as you did, the net
will be much more useful than ever before.

Also visit my homepage - work from home

7:18 AM

 
Anonymous Anonymous said...

An impressive share! I've just forwarded this onto a colleague who had been conducting a little homework on this. And he actually ordered me dinner because I discovered it for him... lol. So allow me to reword this.... Thanks for the meal!! But yeah, thanks for spending the time to discuss this matter here on your blog.

Have a look at my weblog :: GrowXL

8:31 AM

 
Anonymous Anonymous said...

Hi! This post could not be written any better!
Reading this post reminds me of my good old room mate!
He always kept chatting about this. I will forward this write-up to him.
Pretty sure he will have a good read. Thanks for sharing!


Also visit my webpage Muscle REv Xtreme Trials

4:16 AM

 
Anonymous Anonymous said...

Hello to every body, it's my first pay a visit of this website; this website carries amazing and genuinely excellent stuff in support of readers.

Feel free to visit my webpage - Order Xtrasize

10:34 PM

 
Anonymous Anonymous said...

I love your blog.. very nice colors & theme. Did you
create this website yourself or did you hire someone to
do it for you? Plz answer back as I'm looking to construct my own blog and would like to know where u got this from. cheers

Feel free to surf to my web site - Garcinia Cambogia

1:11 PM

 
Anonymous Anonymous said...

Hello it's me, I am also visiting this web page daily, this site is genuinely pleasant and the visitors are truly sharing pleasant thoughts.

Feel free to visit my web site ... Le parfait skin

5:35 PM

 
Anonymous Anonymous said...

Incredible story there. What occurred after? Good luck!



Also visit my webpage Pure Garcinia Cambogia

6:24 PM

 
Anonymous Anonymous said...

Thank you for some other magnificent post. The place else may just anyone get that type of information in such a perfect means of writing?
I've a presentation subsequent week, and I am on the search for such information.

my web site; Working Online

7:19 AM

 
Anonymous Anonymous said...

It's a pity you don't have a donate button! I'd without a doubt donate to this brilliant blog! I suppose for now i'll settle for bookmarking and adding your
RSS feed to my Google account. I look forward to new updates and will
share this website with my Facebook group.

Talk soon!

Review my homepage Test Force Xtreme Muscle Building

3:41 PM

 
Anonymous Anonymous said...

of course like your web-site but you need to take a look at the spelling on several of your posts.

Many of them are rife with spelling problems and I in finding it very troublesome to tell the reality nevertheless I will surely come again again.


my homepage; Order Pur Essence

10:57 AM

 
Anonymous Anonymous said...

You ought to be a part of a contest for one of the finest blogs on the internet.
I will highly recommend this website!

Also visit my web-site ... make money online from home

1:49 PM

 
Anonymous Anonymous said...

Superb post , I just share it with my buddy of Taiwan.
I Stumble UP your internet log post , you can discover an development of website traffic within 24
hours for targeted folks. Cheers

Feel free to surf to my page :: card credit debt help pay

5:43 AM

 
Anonymous Anonymous said...

Valuable info. Lucky me I found your web site by chance, and I'm shocked why this coincidence did not happened earlier! I bookmarked it.

Feel free to surf to my webpage; Garcinia Cambogia Review

4:03 AM

 

Post a Comment

Links to this post:

Create a Link

<< Home