Execute Postgres Functions

[[deleted]]

Dear all,

I'm new in PHP and Postgres. I have to write some functions in Postgres(like Stored Procedures in SQL Server).
Does any body know how to call these functions from PHP.
Let's supose that this function contains only a SELECT statement

Thank in advance
Ardian

[chriskl]

Hi,

If your function is called 'getStuff()' and executes a select, you'd call it like this:

SELECT getStuff();

Note that in 7.2, only SQL functions can return sets, but in 7.3beta C and pl/pgsql functions can also return sets which is cool.

Chris

[[deleted]]

Thanks Chris,

I don't know wich version of Postgres I'm using. Is there any command that shows the Postgres Version. The postgres is running under Linux (Redhat 7.3)

Thanks
Ardian

[[deleted]]

Thanks again. I createt the function with 'SQL' language, but when I execute this function, it returns a set of numbers, not the result of my Select Statement. Is there any way to tell this function to return the result of Select Statement?

Thanks

[chriskl]

To find version:

SELECT version();

You're not running 7.3, as it hasn't been released yet.

I'm personally not familiar with returning sets from SQL functions, so just read the docs.

Chris

[chriskl]

Actually, you might have to write:

SELECT * FROM getStuff();

Chris

[[deleted]]

I'm still having problems.
I tried this "SELECT * FROM getStuff();", but it returns error
'ERROR: parser: parse error at or near "(" '

When I declared the function getStuff() in Postgres, the return type is setof TableName. Is this the right return datatype?

Thanks
ARdian

[Sxooter]

What you need is

select getstuff(arg1,arg2,argn);

Note that in postgresql you don't have to supply a bogus where clause like you do in some other rdbms

[[deleted]]

Thanks Sxooter, but in my case this does not return the output of the Select statement. It returns a set of numbers, instead of the records that the SELECT Statement that is encapsulated inside the getstuff() function returns.

Thanks

[Sxooter]

The problem is that postgresql 7.2 can't return result sets, only a single result. You'll need to play with postgresql 7.3 to get result sets.

So basically, select function() is the right way, but it just can't return a set.