Jul 25

We have been using  PostgresSQL  in our new product Quiz Stack.

Quiz Stack is a SAAS based  product. Which can  conduct,manage,analysis quizzes.We are using rails and postgresql as our backend.It’s subdomain app and hence we decide to use postgresql schema based approach for storing/querying data.

While working on one of the feature It was needed to draw some random fixed questions for each user.So each user has different order of questions which  is maintain for each specific user.

In Mysql it’s called field function to achieve this ,unfortunately there isn’t  anything as of I know in PostgresSql or is it ? :)

Well we can obviously write a function which can help you do the same.Let’s the function name be sortbyid. 

Following code snippet is a function definition

CREATE OR REPLACE FUNCTION sortByID(anyelement, VARIADIC anyarray) RETURNS BIGINT AS $$
				SELECT n FROM (
   			 	SELECT ROW_NUMBER() OVER () AS n, x FROM unnest($2) x)
   			 	 numbered WHERE numbered.x = $1;
			$$ LANGUAGE 'SQL' IMMUTABLE STRICT;

So here how I get the result from Question table  for 3 record with order or id as 2,1,3

Continue reading »

Tagged with:
preload preload preload