I was wondering if its possible to have a setup where just by plugging a raspberry pi via ethernet or usb to a router and the pi being connected to the network everyone trying to access the network will be presented with a captive portal?
No. Captive portal must somehow intercept and redirect the client's traffic.
It would be a disaster if plugging a device into a properly configured network would allow for interception and manipulation of other devices' communication.
Redirection in captive portals is performed usually either using DNS or ICMP.
In the first method the DNS settings must be altered. In a typical scenario, which you described, these settings are provided by the router or access point to the client machines. Just plugging Raspberry Pi into the network will not alter router's (or AP's) settings.
In the second, Pi would have to route packets for the client - again, connecting the device to the network will not alter client's communication path.