PSQL script which performs a different select depending on a variable

Question

Hi I am creating psql reports and at the minute I have two separate reports which do the same thing other than 1 has an extra column.

The scripts install a view in to the database which is used by some php front end to show the report nicely.

I would like to combine the two and wondered if it is possible to do the following inside the view, so it gets executed every time the report is clicked:

IF(access.accessname = 'UNBLINDED')
    SELECT s.site, s.type, s.name etc
ELSE IF(access.accessname = 'BLINDED')
    SELECT s.site, s.name etc

Or if not can anyone else think of a better way to do what I am currently doing with two different scripts?

Thanks for your help

Brian Mains · Accepted Answer · 2012-02-22 00:53:55Z

2

One common way of handling parameterized data sets is to create user defined functions. This moves the data logic to the data layer and allows the application to simply pass in a parameter (ie: SELECT * FROM new_user_function(access.accessname))

Google can help you find detailed examples and tutorials, and this may help get you started: http://wischner.blogspot.com/2009/03/creating-stored-procedure-function.html

Good luck! ~Rose

edited Feb 22, 2012 at 0:53

Brian Mains

50.8k35 gold badges155 silver badges260 bronze badges

answered Feb 21, 2012 at 15:52

Rose

1565 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Aydin Hassan Over a year ago

Thanks, I understand that I'm supposed to do a function. I'm just finding it hard understanding these tutorials. All I want to do is perform some simple selects but one select should contain an extra column depending on access.accessname. Sorry I'm a noob :(

Rose Over a year ago

Where are you getting lost on the tutorials? Maybe I can help clarify.

Aydin Hassan Over a year ago

Well I'm not sure if I should be returning anything or do I just literally just do the select in the function and how to actually call my function. Do I store the function somewhere else in the db and just have a call to the function in my view. Or does the function and the call go in to the view? Thanks for your help

Rose Over a year ago

The function is defined in the database, and you would replace the call to a view with a call to the function.

Aydin Hassan Over a year ago

Okay I have made some progress but I have ran in to a brick wall as I want to return a varying column table. I am receiving this error "a column definition list is required for functions returning "record"" After doing some research it seems that you have to define the columns which is not really helping me, any ideas?

|

Collectives™ on Stack Overflow

PSQL script which performs a different select depending on a variable

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related