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');


56 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

 
Anonymous Anonymous said...

I have been surfing online more than three hours today,
yet I never found any interesting article like yours.

It is pretty worth enough for me. In my opinion, if all website owners and bloggers made good content as
you did, the web will be a lot more useful than ever before.


Here is my web-site ... cenaless, wall-papers.info,

3:13 PM

 
Anonymous Anonymous said...

You must participate in a contest for among the
most effective blogs on the net. I will suggest this web site!



Look into my web-site: Best Acne Products (bestacneproducts26.deviantart.com)

1:20 PM

 
Anonymous Anonymous said...

This post is actually a good one it assists new web people, who are wishing in favor of blogging.


Feel free to surf to my page - jeu machine à sous

8:59 AM

 
Anonymous Anonymous said...

I'm gone to say to my little brother, that he should also go to see this blog on regular basis to take updated from
most up-to-date reports.

Feel free to visit my page - nike corporation

2:53 PM

 
Anonymous Anonymous said...

Hi there! I could have sworn I've visited this site before but after going
through mzny of the posts I realized it's new to me. Nonetheless,
I'm definitely delighted I stumbled upon it and I'll be book-marking it and
checking back frequently!

Look at my blog post - icloud bypass

9:29 PM

 
Anonymous Anonymous said...

Currently it appears like Expression Engine is the
best blogging platform available right now.
(from what I've read) Is that what you're using on your
blog?

Visit my site :: candy crush saga cheats level 30 - -

1:16 AM

 
Anonymous Anonymous said...

Wow, this piece of writing is good, my sister is analyzing such
things, so I am going to tell her.

my blog: goji berry ()

12:34 AM

 
Anonymous Anonymous said...

This is my first time pay a visit at here and i am genuinely pleassant
to read all at alone place.

my website garcinia review (http://socialnetwork.stock-options-picks.com/blogs/viewstory/624948)

3:23 AM

 
Anonymous Anonymous said...

Microsoft may also be flipping the activate free Xbox One games for Xbox LIVE
Gold subscribers this month also. The first part of advice is to take a
look on the exclusive games for the system, see which games
for the list make you drool, and pick that one. Even though Skype may be a free of charge plan about the computer, about the Xbox live you happen to be able to exclusively use this in case you possess the Precious metal request.


Visit my webpage ... Xbox Live Generator

3:04 AM

 
Anonymous Anonymous said...

I simply could not go away your site before
suggesting that I actually enjoyed the usual information a person supply for your guests?
Is gonna be again often to inspect new posts

my web page ... the best pure garcinia cambogia extract

3:19 AM

 
Anonymous Anonymous said...

The look of their accomplice using a intercourse toy is sure to stimulate them in a matter of seconds.


Here is my blog - sexiest vines youtube

9:53 PM

 
Anonymous check out this 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

1:46 AM

 
Anonymous nice stuff said...

It's awesome . i find this many time in google . And finally i got it .

12:49 AM

 
Anonymous check out said...

your blog is providing many important news . thanks for share .

12:57 AM

 
Anonymous my site said...

I read this post very attentively . it's so informative . thanks fore share this .

2:27 AM

 
Anonymous Learn more said...

A portable generator can be a dangerous thing if you don’t know what you’re doing with it. Here are some safety tips before starting the engine of theportable generator reviews and during the engine start-up tips that are useful for you:

2:38 AM

 
Anonymous neat goods said...

A generator is very important thing . But it's most important to know how to use it . otherwise it can make many damage . if anyone know how to use it then it's a useful thing . thanks for share this .

5:36 PM

 
Anonymous my blog said...

generator is generally need for all . it so important . thanks for post .

7:33 PM

 
Anonymous how to learn ? said...

I wanna learn about MS SQL . can you help me ?

2:22 AM

 
Anonymous website said...

Thanks for your good post. Thanks for you can take change comment. The Bangladesh has eleven education boards. 

2:38 AM

 
Anonymous click website said...

This blog is providing beautiful news about about the Liberian Educational Sector. It is nice to know about it this is useful information. And such blogs makes us familiar with the whole world.

12:45 AM

 
Anonymous check my blog update said...

nice post . thanks

2:12 AM

 
Anonymous check out this said...

Very Useful information, this is both good reading for, have quite a few good key points and I learn some new stuff from it too.

11:21 PM

 
Anonymous find out said...

this the most important info what you share . thanks for this .

2:17 AM

 
Anonymous check out website said...

Posts shared useful information and meaningful life, I'm glad to be reading this article and hope to soon learn the next article. thank you

2:06 AM

 
Anonymous how to learn ? said...

are you able to help on your topic ?

3:37 AM

 
Anonymous click said...

This is very nice one and gives indepth information. by the way, yesterday the team of Professional written for me excellent article and good essays ,recommend

1:27 AM

 
Anonymous visit here said...

this so beautiful .

1:14 AM

 
Anonymous visit here said...

i am so impressed . this is really good .

4:05 AM

 
Anonymous my suggestion said...

NICE POST . KEEP THIS .

6:07 PM

 
Anonymous kitchen said...

keep blogging . this is so nice concept.

6:04 PM

 

Post a Comment

<< Home