From mlanka@avineonindia.com Thu Jul  19 09:15:45 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 44ADCF6332A
	for <pgsql-admin@postgresql.org>; Thu, 19 Jul 2012 06:15:45 -0300 (ADT)
Received: from mail.avineonindia.com ([175.101.1.171]
	helo=aipl-2s5532s.avineonindia.com)
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <mlanka@avineonindia.com>) id 1Srmpg-0002Dw-18
	for pgsql-admin@postgresql.org; Thu, 19 Jul 2012 09:15:44 +0000
Received: from MAIL.avineonindia.com (Not Verified[10.0.1.9]) by
	aipl-2s5532s.avineonindia.com with MailMarshal (v6, 8, 3, 9481)
	id <B5007cff80000>; Thu, 19 Jul 2012 14:44:32 +0530
X-Footer: YXZpbmVvbmluZGlhLmNvbQ==
Received: from aiplina82109cp ([10.0.12.99])
	(authenticated user mlanka@avineonindia.com) by MAIL.avineonindia.com
	for pgsql-admin@postgresql.org; Thu, 19 Jul 2012 14:44:31 +0530
Reply-To: <mlanka@avineonindia.com>
From: "Madhu.Lanka" <mlanka@avineonindia.com>
To: <pgsql-admin@postgresql.org>
Subject: need help to write a function in postgresql
Date: Thu, 19 Jul 2012 14:44:28 +0530
Organization: Avineon India Pvt. Ltd.
Message-ID: <000b01cd658e$e66ae920$b340bb60$@avineonindia.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_000C_01CD65BD.00232520"
X-Mailer: Microsoft Outlook 14.0
Thread-Index: Ac1ljtpifI2xg+j8QUyWUWcMcDw/Yw==
Content-Language: en-us
X-Pg-Spam-Score: 0.8 (/)
X-Archive-Number: 201207/40
X-Sequence-Number: 39757

This is a multipart message in MIME format.

------=_NextPart_000_000C_01CD65BD.00232520
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi Friends

 

Can u please help to write a function for the following scenario?

 

I have 3 table's user_roles, principals and roles.

I have to write a function in postgresql which should excepts 2 parameters
(name, password)

With those 2 parameters the query should be executed and return the
result.(Result of the query will be 1 or more rows).

The query is working fine if executed it manually by hardcoding the values
for name and password, but is as to be written in function so that I can
send different values.

 

"select
u.role_id,u.user_id,p.name,p.creation_date,p.telephone_number,p.email,p.rema
rks,p.city,p.state,p.country,p.postal_address,p.company_name,p.department_na
me,p.contact_person,

p.role_group,p.role_description,p.first_name,p.last_name,p.pincode,p.status,
p.department_id,p.designation_id,r.role_name,r.right_edit,r.right_filter,r.r
ight_search,r.right_browse,

r.right_print,
r.right_download,r.right_admin_user,r.right_image_creator,r.right_vector_dow
nload,r.right_wps,r.right_queries_filter,r.right_google_map,r.right_user_man
agement,

r.right_route_analysis,
r.right_legend,r.right_treeview,r.insert,r.update,r.delete,r.query,r.lock
from user_roles u,.principals p,roles r where p.PRINCIPAL_ID = u.USER_ID and


r.role_id = u.role_id and p.name=? and p.password=?"

 

 

Thanks in Advance.

 

Regards

Madhu.Lanka


------=_NextPart_000_000C_01CD65BD.00232520
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40"><head><meta =
http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii"><meta name=3DGenerator content=3D"Microsoft Word 14 =
(filtered medium)"><style><!--
/* Font Definitions */
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri","sans-serif";}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]--></head><body lang=3DEN-US link=3Dblue =
vlink=3Dpurple><div class=3DWordSection1><p class=3DMsoNormal>Hi =
Friends<o:p></o:p></p><p class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal>Can u please help to write a function for the =
following scenario?<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>I have 3 =
table&#8217;s user_roles, principals and roles.<o:p></o:p></p><p =
class=3DMsoNormal>I have to write a function in postgresql which should =
excepts 2 parameters (name, password)<o:p></o:p></p><p =
class=3DMsoNormal>With those 2 parameters the query should be executed =
and return the result.(Result of the query will be 1 or more =
rows).<o:p></o:p></p><p class=3DMsoNormal>The query is working fine if =
executed it manually by hardcoding the values for name and password, but =
is as to be written in function so that I can send different =
values.<o:p></o:p></p><p class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal>&#8220;select =
u.role_id,u.user_id,p.name,p.creation_date,p.telephone_number,p.email,p.r=
emarks,p.city,p.state,p.country,p.postal_address,p.company_name,p.departm=
ent_name,p.contact_person,<o:p></o:p></p><p =
class=3DMsoNormal>p.role_group,p.role_description,p.first_name,p.last_nam=
e,p.pincode,p.status,p.department_id,p.designation_id,r.role_name,r.right=
_edit,r.right_filter,r.right_search,r.right_browse,<o:p></o:p></p><p =
class=3DMsoNormal>r.right_print, =
r.right_download,r.right_admin_user,r.right_image_creator,r.right_vector_=
download,r.right_wps,r.right_queries_filter,r.right_google_map,r.right_us=
er_management,<o:p></o:p></p><p =
class=3DMsoNormal>r.right_route_analysis, =
r.right_legend,r.right_treeview,r.insert,r.update,r.delete,r.query,r.lock=
 from user_roles u,.principals p,roles r where p.PRINCIPAL_ID =3D =
u.USER_ID and <o:p></o:p></p><p class=3DMsoNormal>r.role_id =3D =
u.role_id and <span =
style=3D'background:yellow;mso-highlight:yellow'>p.name=3D?</span> and =
<span =
style=3D'background:yellow;mso-highlight:yellow'>p.password=3D?</span>&#8=
221;<o:p></o:p></p><p class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>Thanks in =
Advance.<o:p></o:p></p><p class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal>Regards<o:p></o:p></p><p =
class=3DMsoNormal>Madhu.Lanka<o:p></o:p></p></div></body></html>
------=_NextPart_000_000C_01CD65BD.00232520--






From spam_eater@gmx.net Thu Jul  19 10:28:43 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id C3AF2F6332A
	for <pgsql-admin@postgresql.org>; Thu, 19 Jul 2012 07:28:43 -0300 (ADT)
Received: from plane.gmane.org ([80.91.229.3])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <sql-pgsql-admin@m.gmane.org>) id 1SrnyM-0000qN-IU
	for pgsql-admin@postgresql.org; Thu, 19 Jul 2012 10:28:43 +0000
Received: from list by plane.gmane.org with local (Exim 4.69)
	(envelope-from <sql-pgsql-admin@m.gmane.org>) id 1Srny6-0008C9-JL
	for pgsql-admin@postgresql.org; Thu, 19 Jul 2012 12:28:26 +0200
Received: from 217.110.94.67 ([217.110.94.67])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-admin@postgresql.org>; Thu, 19 Jul 2012 12:28:26 +0200
Received: from spam_eater by 217.110.94.67 with local (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-admin@postgresql.org>; Thu, 19 Jul 2012 12:28:26 +0200
X-Injected-Via-Gmane: http://gmane.org/
To: pgsql-admin@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: need help to write a function in postgresql
Date: Thu, 19 Jul 2012 12:28:03 +0200
Lines: 63
Message-ID: <ju8nfr$aco$1@dough.gmane.org>
References: <000b01cd658e$e66ae920$b340bb60$@avineonindia.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Complaints-To: usenet@dough.gmane.org
X-Gmane-NNTP-Posting-Host: 217.110.94.67
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.8.1.23) Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
In-Reply-To: <000b01cd658e$e66ae920$b340bb60$@avineonindia.com>
X-Pg-Spam-Score: -0.7 (/)
X-Archive-Number: 201207/41
X-Sequence-Number: 39758

Madhu.Lanka, 19.07.2012 11:14:
> Hi Friends
>
> Can u please help to write a function for the following scenario?
>
> I have 3 table’s user_roles, principals and roles.
>
> I have to write a function in postgresql which should excepts 2 parameters (name, password)
>
> With those 2 parameters the query should be executed and return the result.(Result of the query will be 1 or more rows).
>
> The query is working fine if executed it manually by hardcoding the values for name and password, but is as to be written in function so that I can send different values.
>
> “select u.role_id,u.user_id,p.name,p.creation_date,p.telephone_number,p.email,p.remarks,p.city,p.state,p.country,p.postal_address,p.company_name,p.department_name,p.contact_person,
>
> p.role_group,p.role_description,p.first_name,p.last_name,p.pincode,p.status,p.department_id,p.designation_id,r.role_name,r.right_edit,r.right_filter,r.right_search,r.right_browse,
>
> r.right_print, r.right_download,r.right_admin_user,r.right_image_creator,r.right_vector_download,r.right_wps,r.right_queries_filter,r.right_google_map,r.right_user_management,
>
> r.right_route_analysis, r.right_legend,r.right_treeview,r.insert,r.update,r.delete,r.query,r.lock from user_roles u,.principals p,roles r where p.PRINCIPAL_ID = u.USER_ID and
>
> r.role_id = u.role_id and p.name=? and p.password=?”
>

Something like this:

     create or replace function retrieve_user(uname text, pwd text)
       returns table (role_id integer, user_id integer, name text, .... more columns ...)
     as
     $$
     SELECT u.role_id,
            u.user_id,
            p.name,
            p.creation_date,
            p.telephone_number,
            .... more columns ...
     FROM user_roles u,
          principals p,
          roles r
     WHERE p.principal_id = u.user_id
     AND   r.role_id = u.role_id
     AND   p.name = $1
     AND   p.password = $2
     $$
     language SQL;


Then you can do:
     
     select *
     from retrieve_user('foo', 'bar');

Personally I'd prefer to create view that wraps that select statement and then simply do a

    select *
    from user_view
    where name = 'foo'
    and password = 'bar'

Regards
Thomas






From laurenz.albe@wien.gv.at Thu Jul  19 12:24:14 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 00A5AF6332B
	for <pgsql-admin@postgresql.org>; Thu, 19 Jul 2012 09:24:14 -0300 (ADT)
Received: from relay-fr112.magwien.gv.at ([217.149.225.66])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <laurenz.albe@wien.gv.at>) id 1Srpm8-0005DJ-JZ
	for pgsql-admin@postgresql.org; Thu, 19 Jul 2012 12:24:13 +0000
Received: from localhost (localhost.localdomain [127.0.0.1])
	by relay-fr112.magwien.gv.at (Postfix) with ESMTP id 87D5F4C5A5D;
	Thu, 19 Jul 2012 14:23:59 +0200 (CEST)
X-Virus-Scanned: by amavisd-new using Sophie and ClamAV at magwien.gv.at
Received: from exrat0.host.magwien.gv.at (exrat0.host.magwien.gv.at [--])
	by relay-fr112.magwien.gv.at (Postfix) with ESMTP id 6FAAE4C5A5B;
	Thu, 19 Jul 2012 14:23:59 +0200 (CEST)
Received: from exadv11.host.magwien.gv.at ([--]) by exrat0.host.magwien.gv.at
	with Microsoft SMTPSVC(6.0.3790.4675);
	Thu, 19 Jul 2012 14:23:59 +0200
X-MimeOLE: Produced By Microsoft Exchange V6.5
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: need help to write a function in postgresql
Date: Thu, 19 Jul 2012 14:23:57 +0200
Message-ID: 
 <D960CB61B694CF459DCFB4B0128514C2082E6ED9@exadv11.host.magwien.gv.at>
In-Reply-To: <000b01cd658e$e66ae920$b340bb60$@avineonindia.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [ADMIN] need help to write a function in postgresql
Thread-Index: Ac1ljtpifI2xg+j8QUyWUWcMcDw/YwAGkS0A
References: <000b01cd658e$e66ae920$b340bb60$@avineonindia.com>
From: "Albe Laurenz" <laurenz.albe@wien.gv.at>
To: <mlanka@avineonindia.com>,
	<pgsql-admin@postgresql.org>
X-OriginalArrivalTime: 19 Jul 2012 12:23:59.0255 (UTC)
	FILETIME=[5FAB4270:01CD65A9]
X-Pg-Spam-Score: -5.5 (-----)
X-Archive-Number: 201207/43
X-Sequence-Number: 39760

Madhu.Lanka wrote:
> Can u please help to write a function for the following scenario?

> "select [...] from [...] where [...] and p.name=3D? and =
p.password=3D?"

Don't use "?" for the parameters.
Use "$1" for the first parameter and "$2" for the second.

Yours,
Laurenz Albe



From gandalf@shopzeus.com Fri Aug  3 08:37:25 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 5B836930E1E
	for <pgsql-admin@postgresql.org>; Fri,  3 Aug 2012 05:37:25 -0300 (ADT)
Received: from 10610.x.rootbsd.net ([204.109.60.69] helo=vps001.shopzeus.com)
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <gandalf@shopzeus.com>) id 1SxDNs-0006O0-Qy
	for pgsql-admin@postgresql.org; Fri, 03 Aug 2012 08:37:25 +0000
Received: from [192.168.1.105] (localhost [127.0.0.1])
	by vps001.shopzeus.com (8.14.3/8.14.3) with ESMTP id q738bEL0056037
	for <pgsql-admin@postgresql.org>; Fri, 3 Aug 2012 04:37:15 -0400 (EDT)
	(envelope-from gandalf@shopzeus.com)
Message-ID: <501B8DB7.5050700@shopzeus.com>
Date: Fri, 03 Aug 2012 10:37:11 +0200
From: Laszlo Nagy <gandalf@shopzeus.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120714 Thunderbird/14.0
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: need help to write a function in postgresql
References: <000b01cd658e$e66ae920$b340bb60$@avineonindia.com>
	<ju8nfr$aco$1@dough.gmane.org>
In-Reply-To: <ju8nfr$aco$1@dough.gmane.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201208/16
X-Sequence-Number: 39837


> Then you can do:
>         select *
>     from retrieve_user('foo', 'bar');
>
> Personally I'd prefer to create view that wraps that select statement 
> and then simply do a
>
>    select *
>    from user_view
>    where name = 'foo'
>    and password = 'bar'
It is also better because with a view, you can also do " name is null ". 
But you cannot do that with a function (unless you write unnecessary 
circumstancial code.)



From ringerc@ringerc.id.au Fri Aug  3 08:48:52 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id A0AF1930E1E
	for <pgsql-admin@postgresql.org>; Fri,  3 Aug 2012 05:48:52 -0300 (ADT)
Received: from mail-ob0-f174.google.com ([209.85.214.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ringerc@ringerc.id.au>) id 1SxDYv-0006XA-HN
	for pgsql-admin@postgresql.org; Fri, 03 Aug 2012 08:48:52 +0000
Received: by obbuo13 with SMTP id uo13so784220obb.19
	for <pgsql-admin@postgresql.org>; Fri, 03 Aug 2012 01:48:36 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding
	:x-gm-message-state;
	bh=M84feoLk8J/x4vFtUYamXkshRn4KYelc4asOXtSRESg=;
	b=CWMnWCd8rYsFLLglkCO+qpcKg28e5GYp1m3mQzg9MIhxeGUgJs7mnfdQU7y793jqf6
	HQyvK2+xKB+xXnnoIZimiE0AbwuXTzZluxNtVnveSM86NzJ60cxfMcPtb4rl4h1UBdK6
	bXMFtpGkHqKyaGVJN+5MMmqsIq3jIimx1gTAE/GSqCiI76utHFTjJw16Qm3YPPYMhaCY
	WLrlnB/jJ+E2JRNlvIYagVd3gGLNN5pvopTg3s5oT6aTNiSWo0mGbAF8azDlV8ZaL58Y
	O68VfJaP9oskGT/wPS4wGE2pPOCSbT49QL1/1M/5VqybLz/LWPbX3W9eDiUgz00gY3LM
	vxCA==
Received: by 10.182.212.36 with SMTP id nh4mr2531647obc.37.1343983716386;
	Fri, 03 Aug 2012 01:48:36 -0700 (PDT)
Received: from ayaki.localdomain (124-169-196-180.dyn.iinet.net.au.
	[124.169.196.180])
	by mx.google.com with ESMTPS id i2sm9088036obn.19.2012.08.03.01.48.33
	(version=SSLv3 cipher=OTHER); Fri, 03 Aug 2012 01:48:35 -0700 (PDT)
Message-ID: <501B905E.1050706@ringerc.id.au>
Date: Fri, 03 Aug 2012 16:48:30 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120717 Thunderbird/14.0
MIME-Version: 1.0
To: Laszlo Nagy <gandalf@shopzeus.com>
CC: pgsql-admin@postgresql.org
Subject: Re: need help to write a function in postgresql
References: <000b01cd658e$e66ae920$b340bb60$@avineonindia.com>
	<ju8nfr$aco$1@dough.gmane.org> <501B8DB7.5050700@shopzeus.com>
In-Reply-To: <501B8DB7.5050700@shopzeus.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Gm-Message-State: 
 ALoCoQmR7oxu0o4mkposRdBDQ8rZQzwI0tvbRpOI+AcgG0PmHzEBm0U5P6yzCy4S0EmWZy9xGfK3
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201208/17
X-Sequence-Number: 39838

On 08/03/2012 04:37 PM, Laszlo Nagy wrote:
> It is also better because with a view, you can also do " name is null 
> ". But you cannot do that with a function (unless you write 
> unnecessary circumstancial code.)
>
While I agree with you on the view - among other things, it lets the 
query optimiser push conditions down into the view query - there is a 
reasonable answer to the problem of comparing to NULL. Just use IS 
DISTINCT FROM, eg:

    WHERE name IS DISTINCT FROM _test_variable

"IS DISTINCT FROM" is an equality comparison that treats null as a 
comparable value like any other, so "NULL IS DISTINCT FROM NULL" is 
false. Very handy.

--
Craig Ringer