473,543 Members | 2,454 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

selecting table at execution with PL/PgSQL

I am trying to write a PL/PgSQL function that can be given a table name as an argument at execution time and I don't see any way of doing it. I want the user to be able to specify the table dynamically when the function is run.

As an example, if I try the following:

CREATE OR REPLACE FUNCTION scrub() RETURNS VARCHAR AS '
DECLARE
tl pg_tables%ROWTY PE;
BEGIN
FOR tl IN SELECT * FROM pg_tables LOOP
RAISE INFO ''table %'', tl.tablename;
SELECT * FROM tl.tablename;
END LOOP;
return ''SUCCESS'';
END;
' LANGUAGE 'plpgsql';

I get the following error:

testb=# select scrub();
INFO: table pg_conversion
WARNING: Error occurred while executing PL/pgSQL function scrub
WARNING: line 8 at SQL statement
ERROR: parser: parse error at or near "$1" at character 17
testb=#

Nov 12 '05 #1
1 2126
Bill Nedell wrote:
I am trying to write a PL/PgSQL function that can be given a table
name as an argument at execution time and I don't see any way of doing
it. I want the user to be able to specify the table dynamically when
the function is run.

As an example, if I try the following:

CREATE OR REPLACE FUNCTION scrub() RETURNS VARCHAR AS '
DECLARE
tl pg_tables%ROWTY PE;
BEGIN
FOR tl IN SELECT * FROM pg_tables LOOP
RAISE INFO ''table %'', tl.tablename;
SELECT * FROM tl.tablename;


Try EXECUTE 'SELECT ....

where the execute parameter is created by assembling the select
statement string.

It's all in the documentation.

http://www.postgresql.org/docs/7.3/s...NG-DYN-QUERIES

Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
12557
by: Agoston Bejo | last post by:
Take a look at the following example: table T(i INTEGER, j INTEGER) I want to get the value of i where j is minimal and some conditions apply. (1) SELECT i FROM T WHERE AND j
17
12795
by: Dr NoName | last post by:
Help! I have a table that multiple processes must be able to write to concurrently. However, it for some reason gets locked in exclusive mode. I narrowed it down to one SQL statement + some weirdness with foreign keys. To debug this, I opened two psql sessions and typed in the sql statements manually. Here is the situation: CREATE TABLE...
3
7648
by: joseph speigle | last post by:
hello list, I want to do something like the following: address=# @var = select max(id) from passwd; ERROR: parser: parse error at or near "@" at character 1 address=# var = select max(id) from passwd; ERROR: parser: parse error at or near "var" at character 1 address=# :var = select max(id) from passwd; ERROR: parser: parse error at...
8
3234
by: Együd Csaba | last post by:
Hi All, how can I improve the query performance in the following situation: I have a big (4.5+ million rows) table. One query takes approx. 9 sec to finish resulting ~10000 rows. But if I run simultaneously 4 similar queries it takes nearly 5 minutes instead of 4 times 9 seconds or something near of that. here is a sample query: select ...
1
2556
by: Ramesh | last post by:
hi, I am selecting fields from three table for manupulating data and i want to display total number of records selected. But i am always getting -1 value, eventhough 1000 of records are selected. Below is my code. here strSelectSQL value is strSelectSQL = "Select emp.Empno, emp.FirstName, emp.LastName, emp.DB,...
3
5330
by: Alex Satrapa | last post by:
There's some mention in the (old!) documentation that constraints such as foreign keys won't include data from inheriting tables, eg: CREATE TABLE foo ( id SERIAL PRIMARY KEY ); CREATE TABLE bar ( attribute integer NOT NULL ) INHERITS (foo);
4
2969
by: Scot L. Harris | last post by:
Currently using Postgresql 7.2.4-5.80 with php 4.2.2.-8.0.8 on a redhat 8.0 system. I am writing some php scripts where I want to generate a list of the column names in a particular table that the user selects. I could take the brute force method and hard code the column names but then every time I add a new table or modify an existing one...
1
2060
by: Graeme Hinchliffe | last post by:
Hiya, Not had much experience with tiggers under postgres but am liking them so far. My problem is this. I am writing an updates system, postgres holds the master copy of the database, any changes made to this are logged in an updates table which is monitored by a daemon, which if any updates are spotted propigates them to the remote...
5
5533
by: Miquel van Smoorenburg | last post by:
I have a database with a btree index on the 'removed' field, which is of type 'date'. However it isn't being used: techdb2=> explain select * from lines where removed > CURRENT_DATE; QUERY PLAN ------------------------------------------------------------ Seq Scan on lines (cost=0.00..243.47 rows=2189 width=324) Filter: (removed >...
0
7735
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7347
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7688
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5271
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4895
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3391
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3391
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
968
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
636
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.