Skip site navigation (1) Skip section navigation (2)

Re: need help to write a function in postgresql

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: need help to write a function in postgresql
Date: 2012-07-19 10:28:03
Message-ID: ju8nfr$aco$ (view raw, whole thread or download thread mbox)
Lists: 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.creation_date,p.telephone_number,,p.remarks,,p.state,,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 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 ...)
     SELECT u.role_id,
            .... 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 = $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'


In response to


pgsql-admin by date

Next:From: francescoboccacci@libero.itDate: 2012-07-19 10:40:05
Subject: SSL SYSCALL error: EOF detected
Previous:From: Madhu.LankaDate: 2012-07-19 09:14:28
Subject: need help to write a function in postgresql

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group