pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedneed help to write a function in postgresql
5+ messages / 5 participants
[nested] [flat]
* need help to write a function in postgresql
@ 2012-07-19 09:14 Madhu.Lanka <mlanka@avineonindia.com>
2012-07-19 10:28 ` Re: need help to write a function in postgresql Thomas Kellerer <spam_eater@gmx.net>
2012-07-19 12:23 ` Re: need help to write a function in postgresql Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 2 replies; 5+ messages in thread
From: Madhu.Lanka @ 2012-07-19 09:14 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: need help to write a function in postgresql
2012-07-19 09:14 need help to write a function in postgresql Madhu.Lanka <mlanka@avineonindia.com>
@ 2012-07-19 10:28 ` Thomas Kellerer <spam_eater@gmx.net>
2012-08-03 08:37 ` Re: need help to write a function in postgresql Laszlo Nagy <gandalf@shopzeus.com>
1 sibling, 1 reply; 5+ messages in thread
From: Thomas Kellerer @ 2012-07-19 10:28 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: need help to write a function in postgresql
2012-07-19 09:14 need help to write a function in postgresql Madhu.Lanka <mlanka@avineonindia.com>
2012-07-19 10:28 ` Re: need help to write a function in postgresql Thomas Kellerer <spam_eater@gmx.net>
@ 2012-08-03 08:37 ` Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 08:48 ` Re: need help to write a function in postgresql Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 1 reply; 5+ messages in thread
From: Laszlo Nagy @ 2012-08-03 08:37 UTC (permalink / raw)
To: pgsql-admin
> 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.)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: need help to write a function in postgresql
2012-07-19 09:14 need help to write a function in postgresql Madhu.Lanka <mlanka@avineonindia.com>
2012-07-19 10:28 ` Re: need help to write a function in postgresql Thomas Kellerer <spam_eater@gmx.net>
2012-08-03 08:37 ` Re: need help to write a function in postgresql Laszlo Nagy <gandalf@shopzeus.com>
@ 2012-08-03 08:48 ` Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 0 replies; 5+ messages in thread
From: Craig Ringer @ 2012-08-03 08:48 UTC (permalink / raw)
To: Laszlo Nagy <gandalf@shopzeus.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: need help to write a function in postgresql
2012-07-19 09:14 need help to write a function in postgresql Madhu.Lanka <mlanka@avineonindia.com>
@ 2012-07-19 12:23 ` Albe Laurenz <laurenz.albe@wien.gv.at>
1 sibling, 0 replies; 5+ messages in thread
From: Albe Laurenz @ 2012-07-19 12:23 UTC (permalink / raw)
To: mlanka@avineonindia.com; pgsql-admin
Madhu.Lanka wrote:
> Can u please help to write a function for the following scenario?
> "select [...] from [...] where [...] and p.name=? and p.password=?"
Don't use "?" for the parameters.
Use "$1" for the first parameter and "$2" for the second.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2012-08-03 08:48 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-07-19 09:14 need help to write a function in postgresql Madhu.Lanka <mlanka@avineonindia.com>
2012-07-19 10:28 ` Thomas Kellerer <spam_eater@gmx.net>
2012-08-03 08:37 ` Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 08:48 ` Craig Ringer <ringerc@ringerc.id.au>
2012-07-19 12:23 ` Albe Laurenz <laurenz.albe@wien.gv.at>
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